1 Filter for 2 values from 1 table

[LogiFons]

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!

[v-weiyan1-msft · Accepted solution]

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.

[Ashish_Mathur]

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.

[gmsamborn]

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

Hauliers and Orders.pbix

[LogiFons]

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?

[gmsamborn]

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]

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]

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.