Forum Discussion

LogiFons's avatar
LogiFons
Frequent Visitor
2 years ago
Solved

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 she...
Need Help
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    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.

gmsamborn's avatar
gmsamborn
Super User
2 years ago

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.  

LogiFons's avatar
LogiFons
Frequent Visitor
2 years ago

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.

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    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.

     

    Hauliers and Orders.pbix

     

    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.