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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
pacifist
Helper I
Helper I

List values from other table based on the filter

Hi all, I'm getting a bit desperate but perhaps I just don't understand how the DAX properly works.

 

assume the inputs:

DATASETS table:

IDNAME
ID_1DS_1
ID_2DS_2

 

REPORTS table:

REPORT_IDREPORT_NAMEDATASET_ID
RP_1REPORT_1ID_1
RP_2REPORT_2ID_1
RP_3REPORT_3

ID_2

 

Now, I have a drill through page containing all the details per SINGLE Dataset Id. I want to have a table visual, that will display the "related" report as rows as a filter of that table.
E.g. if the datasets is DS_1

DS_IDREPORT_NAME
DS_1REPORT_1
DS_1REPORT_2

 

I can get the COUNT that's not a problem. However I'm struggling with visualising a LIST, or a measure that returns a LIST or a TABLE if you know what I mean.

The tables are not linked with any relationship in the model becuase of the really rich set if tables and relationships that already prevent it. It has to use some kind of TREATAS or FILTER

Your help is much appreciated

2 ACCEPTED SOLUTIONS
sjoerdvn
Super User
Super User

create a measure like below.
# selected datasets = CALCULATE(COUNTROWS(REPORTS),TREATAS(VALUES(DATASETS[ID]),REPORTS[DATASET_ID]))

use the measure in a filter on the visual where you're showing the reports. use not blank, or larger than zero.

 

View solution in original post

Since you haven't shared all the relevant tables and relationships, have to make some assumptions here. So I assume there is a table workspaces that has a relationship with both datasets and reports. This would be the reason why you can't create an active relationship between report and dataset. So now there is a slicer or page filter on workspaces that filters the datasets, but it will also filter out the reports for those datasets if the reports are in another workspace.
Multiple options here, but one way is to not use any workspace columns in the visual and create measures to do a lookup of the values based on the current report. 
So that could look like this:
report workspace name = CALCULATE(LOOKUPVALUE(WORKSPACES[WS_NAME],WORKSPACES[WS_ID],FIRSTNONBLANK(REPORTS[WS_ID],1)),ALL(WORKSPACES))

View solution in original post

7 REPLIES 7
ERD
Super User
Super User

@pacifist , here is a simple way:

measure_ =
CALCULATE (
    COUNTROWS ( reports ),
    reports[DATASET_ID] = SELECTEDVALUE ( dataset[ID] )
)

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Thank you for responding, however I was missing the last puzzle that was actually adding this measure to the visual filter as per the other response's suggestion!

sjoerdvn
Super User
Super User

create a measure like below.
# selected datasets = CALCULATE(COUNTROWS(REPORTS),TREATAS(VALUES(DATASETS[ID]),REPORTS[DATASET_ID]))

use the measure in a filter on the visual where you're showing the reports. use not blank, or larger than zero.

 

You are a legend! This appears to be working, will have to do more testing! Honeslty, I'm unable to translate it into the human language on how the Visual filters the correct values on a measure that returns a number?! How is it linked?

@sjoerdvn now I have this interesting situation (see screenshot). The calculation gives me correct count, howeve the visual for Reports is not listing all 3 of them (there is another report in another workspace), as it's also filtered by the relationship between the Workspace in the model (the dataset is in relationship with workspaces too).

How can I achieve completely ignoring the relationship between REPORTS - WORKSPACES for this particular visual while still filtering on the DSN_ID?

pacifist_0-1693953999698.png

 

If I don't include any column from Workspaces (e.g. ID, Name), then it lists it out properly:

pacifist_1-1693954555388.png


Now I wonder how to resolve the above. Thank you upfront!

Since you haven't shared all the relevant tables and relationships, have to make some assumptions here. So I assume there is a table workspaces that has a relationship with both datasets and reports. This would be the reason why you can't create an active relationship between report and dataset. So now there is a slicer or page filter on workspaces that filters the datasets, but it will also filter out the reports for those datasets if the reports are in another workspace.
Multiple options here, but one way is to not use any workspace columns in the visual and create measures to do a lookup of the values based on the current report. 
So that could look like this:
report workspace name = CALCULATE(LOOKUPVALUE(WORKSPACES[WS_NAME],WORKSPACES[WS_ID],FIRSTNONBLANK(REPORTS[WS_ID],1)),ALL(WORKSPACES))

that again was teh TOP tip, thank you very much. It does look a bit unintuitive as I thought it would be easy to pull the related data directly from related table (Workspace Name) however haven't thought of having to create a lookup to bypass the filter.

I'd better skill up in DAX as I have so many questions especially on pulling the data from other related tables.

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.