Still can distinguish even if there is no relationship.

[YunJ]

Hi,

I built relationships between these tables.

As you can see, there is no relationship between the blue table and the sub-category table.

However, when I put GMV to the matrix from the blue table, it can still distinguish which sub-category are Makeup, which sub-category are Skincare. (I was thinking there should not blank in Skincare/Makeup). (Although there is relationship between Red table and Sub-category table, but it is single direction).

I also tried to write a YTD measure, and put it to the visual, and as you can see, there is no blank. I think that should be correct because there is no relationship between sub-category and the blue table.

Thanks a lot for your help!

[v-xicai]

Hi @YunJ ,

The TOTALYTD function requires contiguous date selections ,so you may create a calendar table using DAX below, and use it in your [YTD_GMV_blue].

Then create relationship between your 'Blue' table and the calendar table, and change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table. Then when you make some changes or interact in someone table, the other table objects will return corresponding matched result.  See more:Create and manage relationships in Power BI Desktop .

Table: 
Calendar =CALENDARAUTO()

Measure: 
YTD_GMV_blue= TOTALYTD(SUM(Blue[GMV]), Calendar[Date])

Best Regards,

Amy 

Community Support Team _ Amy

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

[YunJ]

Hi @v-xicai 

Thanks for your reply.

My question is, for the first pic, it knows that for Category Skincare, it does not conclude such as Sub-Category Lip stick, so it shows blank. But I didn't add any relationship for the Category and Sub-Category, why it know which Category contains which Sub-Category?

And for the second pic, which I wrote a YTD measure. For each Category, it has the same value and there is no blank, which means it does not know the relationship for the Category and Sub-Category, which I think is correct because there is no relationship at all.

[v-xicai]

Hi @YunJ ,

The reason to change the Cross filter direction of relationships among the these tables above from Single to Both is that it will take these tables treated as a single table.  For example, there is no direct relationship between table 'Blue' and 'Sub-Category', while it can be find matched [Sub-Category] values form path 'Blue' -> 'Category' -> 'Red' -> 'Sub-Category' on the common linked key field.

Then when you make some changes or interact with other table visuals, the other table objects will return corresponding matched result.  See more:Create and manage relationships in Power BI Desktop .

Best Regards,

Amy 

Community Support Team _ Amy

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

[YunJ]

Hello Amy,

But in my first pic, it is single direction 'Category' -> 'Blue' rather than 'Blue' -> 'Category'. Also, it is single direction 'Sub-Category' -> 'Red' rather than 'Red' -> 'Sub-Category'. So why there is relationship between table 'Blue' and 'Sub-Category'?

Thanks a lot for your time!

[amitchandak]

@YunJ , Blue is not joined with Sub Category, how will you get value there

[YunJ]

Because the Category_blue from blue table has relationship with the Category in Category table. 

What I question is, every sub-category in the visual should have the same value. There should no blank shown because there is no sub-category in the blue table.

Thanks

[Mariusz]

Hi @YunJ 

The reason why there is a blank row in the table is the fact that you use two unrelated tables in one visual and in this scenario blank row is automatically added, as per the article below.

https://www.sqlbi.com/articles/blank-row-in-dax/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[YunJ]

Hi @Mariusz Thanks for your reply.

My question is, for the first pic, it knows that for Category Skincare, it does not conclude such as Sub-Category Lip stick, so it shows blank. But I didn't add any relationship for the Category and Sub-Category.

And for the second pic, which I wrote a YTD measure. For each Category, it has the same value and there is no blank, which means it does not know the relationship for the Category and Sub-Category, which I think is correct because there is no relationship at all.

Thanks a lot for your time.