topic Re: List values from other table based on the filter in DAX Commands and Tips

List values from other table based on the filter

pacifist — Tue, 05 Sep 2023 10:17:42 GMT

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:

ID	NAME
ID_1	DS_1
ID_2	DS_2

REPORTS table:

REPORT_ID	REPORT_NAME	DATASET_ID
RP_1	REPORT_1	ID_1
RP_2	REPORT_2	ID_1
RP_3	REPORT_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_ID	REPORT_NAME
DS_1	REPORT_1
DS_1	REPORT_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

Re: List values from other table based on the filter

sjoerdvn — Tue, 05 Sep 2023 15:08:50 GMT

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.

Re: List values from other table based on the filter

ERD — Tue, 05 Sep 2023 16:10:12 GMT

pacifist , here is a simple way:

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

Re: List values from other table based on the filter

pacifist — Tue, 05 Sep 2023 21:48:15 GMT

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!

Re: List values from other table based on the filter

pacifist — Tue, 05 Sep 2023 21:49:40 GMT

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?

Re: List values from other table based on the filter

pacifist — Tue, 05 Sep 2023 22:56:54 GMT

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?

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

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

Re: List values from other table based on the filter

sjoerdvn — Thu, 07 Sep 2023 15:31:15 GMT

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

Re: List values from other table based on the filter

pacifist — Sun, 10 Sep 2023 21:51:58 GMT

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.