cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GOEST Frequent Visitor
Frequent Visitor

Visualize filtered measure data

Hi!

 

I am looking for a solution to the following problem: With the help from this forum I have generate two measures "count" and "TotalHours_Measure". Now I want to show the monthly "count" data in relation by the department. Has anybody an idea how I could do this?

 

Best regards

Stefan

 

count_tab.jpgcount.jpgTotalHours_Measure.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Visualize filtered measure data

Hi @GOEST,

 

You should be able to follow steps below to get your expected result.

 

1. Use the formula below to create a calculate table first.

SummaryTable = FILTER (
            SUMMARIZECOLUMNS (
               pbitest[DEP],
			   pbitest[NAME],
               pbitest[DATE],
                "TotalHours",[TotalHours_Measure]
            ),
            [TotalHours] > 10
        )

 

2. Create an individual Date table(using CALENDARAUTO or CALENDAR Function (DAX)), and create a relationship between the Date table and created "SummaryTable" using the date column.

 

3. Then you should be able to use the formula below to show the monthly "count" data in relation by the department.

Count = 
IF (
    HASONEVALUE ( pbitest[DEP] ),
    CALCULATE (
        COUNTROWS ( 'SummaryTable' ),
        FILTER (
            'SummaryTable',
            'SummaryTable'[DEP] = VALUES ( pbitest[DEP] )
        )
    ),
    COUNTROWS ( 'SummaryTable' )
)

result2.PNG

 

Here is the modified sample pbix file for your reference.Smiley Happy

 

Regards

4 REPLIES 4
v-ljerr-msft Super Contributor
Super Contributor

Re: Visualize filtered measure data

Hi @GOEST,

 

Could you post your table structure and sample data with your expected result in this case? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox, and post the link here. Do mask sensitive data before uploading.Smiley Happy

 

Regards

GOEST Frequent Visitor
Frequent Visitor

Re: Visualize filtered measure data

Hi @v-ljerr-msft!

 

Thanks for your reply. You can find the files on:

 

https://www.dropbox.com/sh/cdeuki9k0x4lksy/AAClOIrREfCrcbeKUkRxIXkDa?dl=0

Best regards

Stefan

v-ljerr-msft Super Contributor
Super Contributor

Re: Visualize filtered measure data

Hi @GOEST,

 

You should be able to follow steps below to get your expected result.

 

1. Use the formula below to create a calculate table first.

SummaryTable = FILTER (
            SUMMARIZECOLUMNS (
               pbitest[DEP],
			   pbitest[NAME],
               pbitest[DATE],
                "TotalHours",[TotalHours_Measure]
            ),
            [TotalHours] > 10
        )

 

2. Create an individual Date table(using CALENDARAUTO or CALENDAR Function (DAX)), and create a relationship between the Date table and created "SummaryTable" using the date column.

 

3. Then you should be able to use the formula below to show the monthly "count" data in relation by the department.

Count = 
IF (
    HASONEVALUE ( pbitest[DEP] ),
    CALCULATE (
        COUNTROWS ( 'SummaryTable' ),
        FILTER (
            'SummaryTable',
            'SummaryTable'[DEP] = VALUES ( pbitest[DEP] )
        )
    ),
    COUNTROWS ( 'SummaryTable' )
)

result2.PNG

 

Here is the modified sample pbix file for your reference.Smiley Happy

 

Regards

GOEST Frequent Visitor
Frequent Visitor

Re: Visualize filtered measure data

Hi @v-ljerr-msft!

 

Thanks for your answer. That looks realy good also with my real data! Smiley Happy

 

BEst regards

Stefan