always include value in filter?

  • pietlinden

    SSC Guru

    Points: 62365

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442116

    pietlinden - Friday, May 11, 2018 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.

    You have ben around long enough to know what is needed to help.

  • pietlinden

    SSC Guru

    Points: 62365

    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?

  • Lynn Pettis

    SSC Guru

    Points: 442116

    pietlinden - Saturday, May 12, 2018 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?

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply