Forum Discussion
Use multiple selectors to dynamically join tables to create calculation with DAX and Power BI
- Anonymous3 years ago
Hi JFG1234 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create two round dimension tables
2. Create two separated slicers and apply the round field of the above dimension tables
3. Create the measures as below to get the result for the different round selectors
Result Round A = VAR _selr1 = SELECTEDVALUE ( 'Round A'[Round] ) RETURN CALCULATE ( SUM ( 'Table'[Result] ), FILTER ( 'Table', 'Table'[Round] = _selr1 ) )Result Round B = VAR _selr1 = SELECTEDVALUE ( 'Round B'[Round] ) RETURN CALCULATE ( SUM ( 'Table'[Result] ), FILTER ( 'Table', 'Table'[Round] = _selr1 ) )4. Create a dimension table as below using "Enter data" method
5. Create the measures as below to get the count of IDs for different status
Measure = VAR _selstatus = SELECTEDVALUE ( 'Status'[Status] ) VAR _selr1 = SELECTEDVALUE ( 'Round A'[Round] ) VAR _selr2 = SELECTEDVALUE ( 'Round B'[Round] ) VAR _tab = SUMMARIZE ( FILTER ( 'Table', 'Table'[Round] IN { _selr1, _selr2 } ), 'Table'[ID], "@r1", [Result Round A], "@r2", [Result Round B] ) RETURN SWITCH ( _selstatus, "Added", COUNTX ( FILTER ( _tab, ISBLANK ( [@r1] ) && NOT ( ISBLANK ( [@r2] ) ) ), [ID] ), "Removed", COUNTX ( FILTER ( _tab, ISBLANK ( [@r2] ) && NOT ( ISBLANK ( [@r1] ) ) ), [ID] ), "Negative", COUNTX ( FILTER ( _tab, NOT ( ISBLANK ( [@r1] ) ) && NOT ( ISBLANK ( [@r2] ) ) && [@r2] - [@r1] < 0 ), [ID] ), "No Change", COUNTX ( FILTER ( _tab, NOT ( ISBLANK ( [@r1] ) ) && NOT ( ISBLANK ( [@r2] ) ) && [@r2] - [@r1] = 0 ), [ID] ), "Positive", COUNTX ( FILTER ( _tab, NOT ( ISBLANK ( [@r1] ) ) && NOT ( ISBLANK ( [@r2] ) ) && [@r2] - [@r1] > 0 ), [ID] ) ) + 0Measure 2 = SUMX ( VALUES ( 'Status'[Status] ), [Measure] )6. Create a matrix visual as below screenshot
Best Regards
Hi JFG1234 ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create two round dimension tables
2. Create two separated slicers and apply the round field of the above dimension tables
3. Create the measures as below to get the result for the different round selectors
Result Round A =
VAR _selr1 =
SELECTEDVALUE ( 'Round A'[Round] )
RETURN
CALCULATE (
SUM ( 'Table'[Result] ),
FILTER ( 'Table', 'Table'[Round] = _selr1 )
)Result Round B =
VAR _selr1 =
SELECTEDVALUE ( 'Round B'[Round] )
RETURN
CALCULATE (
SUM ( 'Table'[Result] ),
FILTER ( 'Table', 'Table'[Round] = _selr1 )
)
4. Create a dimension table as below using "Enter data" method
5. Create the measures as below to get the count of IDs for different status
Measure =
VAR _selstatus =
SELECTEDVALUE ( 'Status'[Status] )
VAR _selr1 =
SELECTEDVALUE ( 'Round A'[Round] )
VAR _selr2 =
SELECTEDVALUE ( 'Round B'[Round] )
VAR _tab =
SUMMARIZE (
FILTER ( 'Table', 'Table'[Round] IN { _selr1, _selr2 } ),
'Table'[ID],
"@r1", [Result Round A],
"@r2", [Result Round B]
)
RETURN
SWITCH (
_selstatus,
"Added", COUNTX ( FILTER ( _tab, ISBLANK ( [@r1] ) && NOT ( ISBLANK ( [@r2] ) ) ), [ID] ),
"Removed", COUNTX ( FILTER ( _tab, ISBLANK ( [@r2] ) && NOT ( ISBLANK ( [@r1] ) ) ), [ID] ),
"Negative",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] < 0
),
[ID]
),
"No Change",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] = 0
),
[ID]
),
"Positive",
COUNTX (
FILTER (
_tab,
NOT ( ISBLANK ( [@r1] ) )
&& NOT ( ISBLANK ( [@r2] ) )
&& [@r2] - [@r1] > 0
),
[ID]
)
) + 0Measure 2 = SUMX ( VALUES ( 'Status'[Status] ), [Measure] )
6. Create a matrix visual as below screenshot
Best Regards
Awesome work, unreal!