May 11, 2018 at 6:31 pm
I have several fact tables and a Schools dimension table. IS there a way to always include a specific school in a filter (like permanently selected).
I was thinking UNION() the filtered result and add the school back using VALUES() but no joy.
May 12, 2018 at 10:33 am
pietlinden - Friday, May 11, 2018 6:31 PMI have several fact tables and a Schools dimension table. IS there a way to always include a specific school in a filter (like permanently selected).
I was thinking UNION() the filtered result and add the school back using VALUES() but no joy.
You have ben around long enough to know what is needed to help.
May 12, 2018 at 4:37 pm
Re-reading my question, you're right... that's not answerable as stated. I'm doing this in PowerBI / DAX, so I have a simple star schema with Customers and Sales ...
CREATE TABLE Customer(
CustomerID INT IDENTITY(1,1),
FIrstName VARCHAR(20),
LastName VARCHAR(20)
CONSTRAINT pkCustomer PRIMARY KEY (CustomerID));
CREATE TABLE Sales(
CustomerID INT NOT NULL,
SaleDate DATE NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice MONEY,
CONSTRAINT fkCustomerID FOREIGN KEY CustomerID REFERENCES Customer(CustomerID));
Say I want to compare a single customer (my company/us) to all other customers in the Customers table, so my company *always* on the pivot. I could create a measure to do it:
Total Units = SUM(Sales[Quantity])
and then filter for just the customer I want in another measure:
Customer12 Total Units = CALCULATE([Total Units],CustomerID=12)
The rub is that I have a ton of measures and several fact tables, and I want to basically force "Customer12" to show up in all of the visuals. I tried using UNION() to append "Customer12" to the selected group of customers, but no joy. Something like
UNION('Customers[CustomerID],12)
Do I have to do something like use FILTER() to create return two tables (one with just Customer12, one with everyone else), and then union those back together?
May 14, 2018 at 1:03 am
pietlinden - Saturday, May 12, 2018 4:37 PMRe-reading my question, you're right... that's not answerable as stated. I'm doing this in PowerBI / DAX, so I have a simple star schema with Customers and Sales ...CREATE TABLE Customer(
CustomerID INT IDENTITY(1,1),
FIrstName VARCHAR(20),
LastName VARCHAR(20)
CONSTRAINT pkCustomer PRIMARY KEY (CustomerID));CREATE TABLE Sales(
CustomerID INT NOT NULL,
SaleDate DATE NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice MONEY,
CONSTRAINT fkCustomerID FOREIGN KEY CustomerID REFERENCES Customer(CustomerID));Say I want to compare a single customer (my company/us) to all other customers in the Customers table, so my company *always* on the pivot. I could create a measure to do it:
Total Units = SUM(Sales[Quantity])
and then filter for just the customer I want in another measure:
Customer12 Total Units = CALCULATE([Total Units],CustomerID=12)The rub is that I have a ton of measures and several fact tables, and I want to basically force "Customer12" to show up in all of the visuals. I tried using UNION() to append "Customer12" to the selected group of customers, but no joy. Something like
UNION('Customers[CustomerID],12)Do I have to do something like use FILTER() to create return two tables (one with just Customer12, one with everyone else), and then union those back together?
Still not there. How about some sample data that represents the problem domain and the expected results based on that sample data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply