Forum Discussion

rpablo's avatar
rpablo
New Member
3 years ago
Solved

Card value based on a table visual

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:

 

IDCreatedSeatWorkspaceAPP
101-01-2023345AX43
231-01-2023367PX45
301-02-2023467RB09
428-02-2023520GB56

 

In my WorkspaceAPP table, I have values like:

IDCreatedAppConfig
AX4301-01-20211
PX4501-01-20212
RB0901-01-20213
GB5601-01-20214

 

In my AppConfig table, I have values like:

 

IDApp Name
1A
2B
3C
4D

 

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 NameSession Date
A01-01-2023
B31-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.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      rpablo
      New Member
      3 years ago

      Thank you Anonymous 

      That worked as I expected.