Need help in one to many relationship

[melvin9900]

Hello,

   I am new to DAX world, so any help is much appreciated.

Workin on a Salesreport , that has two slicer Salesperson and Month.

I have to display a table with Products and rank for the selected Salesperson and month.

Source data : Tabular Model

Table 1 : Products             Table 2:  SalesPerson              Table 3 : SalesDetails

                                

Based on the Selection of Salesperson and Month , the report should display all the Products and their corresponding rank from SalesDetails table . 

Conditions :  1.  Should display all Products and if the Product rank doesnt exist in the SalesDetails table then it should display it as N/A. 

2 . If a Salesperson/Month combination doesnt have any rank for the Product C1  but have Rank for the other Products then C1 rank should be defaulted to 1.

For example : For the Salesperson = Ben and Month = 2/1/2022

Expected output : 

For the Salesperson = Ben and Month = 3/1/2022.

Even though C1 rank is not in the SalesDetails table but it has rank for the other Products so the C1 should be defaulted to 1.

Same with Salesperson = Jon/King and Month = 3/1/2022

           

For the Salesperson = Les/Wes and Month = 2/1/2022 . No data available so all should be 'N/A'

     

The tabular model has many to 1 relationship with filter on both sides between the table SalesDetails and Product. But the report table shows only the Products that are present in the Salesdetails table for that Salesperson and month combination. 

For Ben it shows only A1, B1  and C1 for the month 2/1/2022.

So I need help to show all the product names in the table and display 'N/A' if that doesnt exist in the SalesDetails table.

Thank you.

[v-chenwuz-msft]

Hi @melvin9900 ,

Please use the field from dimensional tables ( Products ) in the table visual.

power bi automatically hides rows with empty results, so that more attention can be paid to the items with data. You can turn off the function as @vapid128 said. Or you can defind the result by youself via measure.

Measure refer the following:

rank =
IF (
    ISBLANK ( MAX ( 'SalesDetails'[Rank] ) ),
    "N/A",
    MAX ( 'SalesDetails'[Rank] )
)

And the result:

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[melvin9900]

Thank you for the Solution. The condition 1 works fine and need some help on the Condition 2 below.

Conditions :  1.  Should display all Products and if the Product rank doesnt exist in the SalesDetails table then it should display it as N/A. 

2 . If a Salesperson/Month combination doesnt have any rank for the Product C1  but have Rank for the product (A1 or B1 ) then C1 rank should be defaulted to 1. 

In your sameple report , for the selected filters C1 should be 1 because the product A1 or B1 is not N/A.

[v-chenwuz-msft · Accepted solution]

Hi @melvin9900 ,

Please try this code:

rank =
VAR _c =
    COUNTROWS (
        FILTER ( ALLSELECTED ( SalesDetails ), [Product] = "A1" || [Product] = "B1" )
    )
RETURN
    IF (
        ISBLANK ( MAX ( 'SalesDetails'[Rank] ) ),
        IF ( SELECTEDVALUE ( Products[Product Name] ) = "C1" && _c > 0, 1, "N/A" ),
        MAX ( 'SalesDetails'[Rank] )
    )

Result:

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vapid128]

Use Product Name in table 1 instead product in table 3

Make the relationship cross filter diretion "BOTH"

Right click on Product Name, click show items with no data.