Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
YunJ
Post Prodigy
Post Prodigy

Still can distinguish even if there is no relationship.

Hi,

I built relationships between these tables.

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

Capture.PNG

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).

Capture3.PNG

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.

Capture4.PNGCapture5.PNG

Thanks a lot for your help!

8 REPLIES 8
v-xicai
Community Support
Community Support

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.

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
Community Support
Community Support

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.

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
Super User
Super User

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

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
Community Champion
Community Champion

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

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors