topic Re: Need help in one to many relationship in DAX Commands and Tips

Need help in one to many relationship

melvin9900 — Thu, 16 Jun 2022 20:09:34 GMT

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.

Re: Need help in one to many relationship

vapid128 — Fri, 17 Jun 2022 01:44:08 GMT

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.

Re: Need help in one to many relationship

v-chenwuz-msft — Tue, 21 Jun 2022 09:49:26 GMT

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.

Re: Need help in one to many relationship

melvin9900 — Tue, 21 Jun 2022 16:35:04 GMT

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.

Re: Need help in one to many relationship

v-chenwuz-msft — Wed, 22 Jun 2022 03:14:18 GMT

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.