Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ScottG
Frequent Visitor

How can I select 2 (or more) Members of one Dimension in DAX?

Good Day,

 

I am trying to provide something like MDX Set behaviour in DAX, (my appologies I am quite new to DAX).

 

Please consider the following:

 

Dim_Simulation

id, name

1, Simulation 1

2, Simulation 2

3, Simulation 3

 

Dim_Start_Simulation

id, name

1, Simulation 1

2, Simulation 2

3, Simulation 3

 

Dim_End_Simulation

id, name

1, Simulation 1

2, Simulation 2

3, Simulation 3

 

where:

- Dim_Simulation is related to the fact table on the id column.

- Dim_Start_Simulation and Dim_End_Simulation are used in DAX variance calculations.

 

Image for Forum Post.png

 

Now I would like to make a graph at the top (orange area in image) of my Power BI Page which has the two (assuming distinct) simulations selected in slicers from Start Simulation and End Simlation as members from Dim_Simulation...

 

My question is how does one go about this?

1 REPLY 1
v-sihou-msft
Employee
Employee

@ScottG

 

In your canvas, if you select Simulation1 for Start_Simulation slicer and Simulation2 for End_Simulation slicer at the same time, no line graph will be displayed. Because there is no Sales (I assume it’s sales here) corresponding to Simulation1 as well as Simulation3. Each sales always belongs to one id.

 

However, you can get the expected result by changing your slicers. Use name in Dim_Simulation as the first slicer, and use another “measure slicer” to decide who is the start simulation. This should work when you only have two simulations selected in the slicer. There is no need to use Dim_Start_Simulation and Dim_End_Simulation anymore. Please refer to following steps:

 

I assume that your fact table is like below

1.jpg

 

  1. Create a new Subtract_Measure Table by click “Enter Data”. So that you can select the proper measure you wanted in the slicer later.
    2.png
  2. Create three measures with below formulas.
    SalesOf_MaxSelectedID =
    CALCULATE (
        SUM ( Fact_Table[sales] ),
        FILTER ( Fact_Table, Fact_Table[id] = MAX ( Fact_Table[id] ) ),
        ALLEXCEPT ( Dim_Simulation, Dim_Simulation[name] ),
        ALLEXCEPT ( Fact_Table, Fact_Table[Calendar_Year] )
    )
    
    SalesOf_MinSelectedID =
    CALCULATE (
        SUM ( Fact_Table[sales] ),
        FILTER ( Fact_Table, Fact_Table[id] = MIN ( Fact_Table[id] ) ),
        ALLEXCEPT ( Dim_Simulation, Dim_Simulation[name] ),
        ALLEXCEPT ( Fact_Table, Fact_Table[Calendar_Year] )
    )
    
    Sales_Subtract = 
    IF (
        HASONEVALUE ( Subtract_Measure[Measure] ),
        SWITCH (
            FIRSTNONBLANK ( Subtract_Measure[Measure], Subtract_Measure[Measure] ),
            "Simulation_MaxNum - Simulation_MinNum", [SalesOf_MaxSelectedID] - [SalesOf_MinSelectedID],
            "Simulation_MinNum - Simulation_MaxNum", [SalesOf_MinSelectedID] - [SalesOf_MaxSelectedID]
        ),
        BLANK ()
    )
    
  3. Drag the Line Char, Matrix and two slicers into canvas. Select two simulations and one measure in two slicers.
    3.png

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.