Forum Discussion
1 Filter for 2 values from 1 table
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
_Result
Good 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?
Hi LogiFons
In my example I created 2 non-active realtionships between the fact table and Haulier dimension table that I created in Power Query.
Neither of these relationships are active so your slicer doesn't filter like it would if one of the relationships was active.
Hi,
I have deleted the relation between both tables, but i still become this message.
My Data Table does have some other relationships with some other Dim tables.
Could this have something to do with this message?
I am trying to find out for some several hours.
Hi LogiFons
There needs to be 2 inactive relationships between your fact table and your date table.
In my example, they are as follows:
1:* (single, inactive) between Haulier[Haulier] and Orders[Haulier]
1:* (single, inactive) between Haulier[Haulier] and Orders[NCR responsible]
There can't be another active relationship between those tables.
In your model, I can see what looks like 2 many-to-many relationships. If possible, I try to find a way too remove/change them. It also looks like there is at least 5 more tables related to tblDataInput. Can you explain these?
Also, make sure that 'Haulier (Ritnummer)' has no duplicates.
I think I might need a pbix with sample data that covers the problem.