Forum Discussion
1 Filter for 2 values from 1 table
Good evening all,
I am working on a performance dashboard for my hauliers.
This shows me the driven trips of a filtered haulier and the total score of his NCR's.
Both should be visible in 1 sheet.
The table "DataInput", shows me the [Ordenumber], name of [Haulier}, nameof [NCR Responsible] haulier and the [Score].
Table : DataInput
| Ordernumber | Haulier | NCR Responsible | Score | |
| 1001 | HaulierBleu | HaulierRed | 3 | |
| 1002 | HaulierBlue | |||
| 1003 | HaulierBlack | HaulierBlack | 1 | |
| 1004 | HaulierRed | HaulierRed | 1 | |
| 1005 | HaulierGreen |
When i am using the Haulier Filter in my dashboard i will need 2 visuals:
1. Overview of orders (trips), this works, no problem.
2. Overview of the Score.
Here starts my problem.
When i filter the haulier for example HaulierRed, the sum of the NCR [Score] is 1 instead of 4, because the filter hides the HaulierBlue, which also has a [NCR responsible] [Score] of 3 for haulierRed.
How can i use the Hauliers Filter,
Showing me the total Ordernumbers AND Showing the summarized Score of 4 of NCR responsible for HaulierRed?
-Haulier names are equal to NCR responsible.
I was thinking of copying the table and use the relation from Haulier and NCR Responsible, but i can't get this done.
Do you have any idea?
Thank you!
Hi LogiFons ,
Based on the sample and description you provided, Please try the following steps:
1.You can create a calcualted table.
Table 2 = DISTINCT('DataInput'[Haulier])There is no relationship between the two tables.
2. Use the following code to create a Measure.
Measure = VAR _sel = ALLSELECTED('Table 2'[Haulier]) RETURN IF(ISFILTERED('Table 2'[Haulier]), IF( MAX('DataInput'[NCR Responsible]) IN _sel, 1 ), 1 )3. Select your visual object, put the measure in the “Filters on this visual” section, and filter it by “Measure is 1”.
The field of the slicer is from Table 2. When you select "HaulierRed" in the slicer, Result is as below.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi,
See if it is any better if you use the "Unpivot Other Columns" feature in Power Query. Select the Order Number and Score columns, right click and select "Unpivot Other Columns". Use this table for building your visuals.
Hi LogiFons ,
Based on the sample and description you provided, Please try the following steps:
1.You can create a calcualted table.
Table 2 = DISTINCT('DataInput'[Haulier])There is no relationship between the two tables.
2. Use the following code to create a Measure.
Measure = VAR _sel = ALLSELECTED('Table 2'[Haulier]) RETURN IF(ISFILTERED('Table 2'[Haulier]), IF( MAX('DataInput'[NCR Responsible]) IN _sel, 1 ), 1 )3. Select your visual object, put the measure in the “Filters on this visual” section, and filter it by “Measure is 1”.
The field of the slicer is from Table 2. When you select "HaulierRed" in the slicer, Result is as below.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi LogiFons
Would something like these measures work? (I added 2 inactive relationships.)
Total Score = VAR _H_Total = CALCULATE( SUM( 'Orders'[Score] ), USERELATIONSHIP( Orders[Haulier], Haulier[Haulier] ) ) VAR _N_Total = CALCULATE( SUM( 'Orders'[Score] ), USERELATIONSHIP( Orders[NCR Responsible], Haulier[Haulier] ), FILTER( 'Orders', 'Orders'[Haulier] <> 'Orders'[NCR Responsible] ) ) VAR _Result = _H_Total + _N_Total RETURN _Result Total Orders = VAR _H_Total = CALCULATE( DISTINCTCOUNT( 'Orders'[Ordernumber] ), USERELATIONSHIP( Orders[Haulier], Haulier[Haulier] ) ) VAR _N_Total = CALCULATE( DISTINCTCOUNT( 'Orders'[Ordernumber] ), USERELATIONSHIP( Orders[NCR Responsible], Haulier[Haulier] ), FILTER( 'Orders', 'Orders'[Haulier] <> 'Orders'[NCR Responsible] ) ) VAR _Result = _H_Total + _N_Total RETURN _ResultGood Morning gmsamborn,
Thank you for your fast response,
I understand the code and as far as i can see, this should work.I am not far enough yet to make this code myself unfortunatly.
But this will come, i am studying DAX at this moment.🙂
When i am using your code, i become the message:
USERELATIONSHIP function can only use the two columns refernces partticipating in relationship.
Ritnummer[Vervoerder] = Haulier[Haulier]
tblDataInput[Haulier] = Data[Haulier]I made a copy of the "DataInput" table, removed the not necassary columns and named this table Haulier (Ritnummer).
I am having an active Many-to-Many Cardinality in my relations for both sides.
Should this be the problem for this measure?
