Card value based on a table visual

[rpablo]

Hi,

I'm building a report with multiple tables, and I would like to present a specific number of applications for the selected time period in a Card.

In my schema I have my main table as "Sessions", related to another table named "WorkspaceAPP", and that last one related to an "AppConfig" table.

In my Sessions table I have values like:

ID	Created	Seat	WorkspaceAPP
1	01-01-2023	345	AX43
2	31-01-2023	367	PX45
3	01-02-2023	467	RB09
4	28-02-2023	520	GB56

In my WorkspaceAPP table, I have values like:

ID	Created	AppConfig
AX43	01-01-2021	1
PX45	01-01-2021	2
RB09	01-01-2021	3
GB56	01-01-2021	4

In my AppConfig table, I have values like:

ID	App Name
1	A
2	B
3	C
4	D

My Sessions table relates to the WorkspaceAPP table using the WorkspaceAPP and ID columns respectively. 

My WorkspaceAPP table and AppConfig table relate using the AppConfig and ID columns respectively.

I've created a visual table to show the app name used in a specific period. I.e. January. The table will look like this:

App Name	Session Date
A	01-01-2023
B	31-01-2023

And I would like to display a "Distinct App Name Count" card with the value 2. But instead, when I pull the App Name value to my card, I get a 4 (total distinct count of apps).

I know I'm missing a step, but I don't know which one.

Thank you in advance for your help.

Regards.

[Anonymous · Accepted solution]

Hi @rpablo ,

If your model set as below screenshot, then create a measure as below to get the distinct count of App Name to replace the original value field in Card visual. 

Distinct App Name Count = 
VAR _wkapp =
    CALCULATETABLE (
        VALUES ( 'Sessions'[WorkspaceAPP] ),
        ALLSELECTED ( 'Sessions' )
    )
VAR _appconfig =
    CALCULATETABLE (
        VALUES ( 'WorkspaceAPP'[AppConfig] ),
        FILTER ( 'WorkspaceAPP', 'WorkspaceAPP'[ID] IN _wkapp )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'AppConfig'[App Name] ),
        FILTER ( 'AppConfig', 'AppConfig'[ID] IN _appconfig )
    )

Best Regards

[rpablo]

Thank you @Anonymous 

That worked as I expected.