Pivot table as a source table

[Anonymous]

Hello,

I am new to the DAX and will need some support by you. The problem I want to solve is described on the picture attached.

Thank you in advance.

[MFelix]

Hi @Anonymous ,

To my understandi you want to create a new table using DAX that gets distinct values for names and the count of total calls correct?

Try this code to do a new table:

Table =
CALCULATETABLE (
    SUMMARIZE ( ALL ( Table[Indv Name] ); Table[Indv Name]; "TotalCalls"; COUNT ( Table[Indv Name] ) )
)

Be aware that this new table does not have in consideration any other filters like time or something similar to interact with the rest of the report, so if you for example do a slicer for time it will not change the results of this table

Regards,

MFelix

[Anonymous]

Hi MFelix,

You are right that this way I will not have time filtering this way will sum all calls for all periods for this for this Indv Name.

I would like to assign the total calls for each distinct Indv Name for the selected period of time. When I select for example 01.2019 to 03.2019, I would like to see column with Indv Name (distinct) and its total calls calculated based on this.

The way PowerBI doing it like pivot table in excel is what I need to group by distinct Indv Name and its total calls from the main table.

In raw data for on each date have Indv Name (repeated) and TotalCall column which is always 1 as value in the cell.

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following table:

 DistinctCount = GENERATESERIES(0; 200; 1)

The second argument can be change to the maximum value you  want, it doesnt matter the number since the next measure will filter out this table.

Create the following measure:

User Count histogram=
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE ( Table1; Table1[Users]; "CountOfUsers"; COUNT ( Table1[Users] ) );
            [CountOfUsers] = SELECTEDVALUE ( 'DistinctCount'[DistinctCount] )
        )
    )
)

Now use the column of DistinctCount  table on your X-Axis and the measure on your values this will allow you to have slicers on dates and on users.

Be aware that you can define if the values without data are viewed in the X-Axis changing from categorical to continuous on the options of the visual

Check PBIX file attach.

This solution is based on this blog post from PowerPivot(Pro).

Regards,

MFelix

[Anonymous]

Hi @MFelix 

Thank you so much for your help. It is working and it's great.  I made some changes to meet my data requerements. Here is the sample DAX:

# of CustomersX =
CALCULATE (
COUNTROWS (
FILTER (
SUMMARIZE ( Table, Table[CustomerID], "CountOfUsers", DISTINCTCOUNT(Table[CallsID]),

[CountOfUsers] = SELECTEDVALUE ( 'DistinctCount'[DistinctCount] )
)
)
)+0

I put +0 at the end to start the line from 0 on x-axis.

Thank you again.

[MFelix]

Hi @Anonymous ,

Good I could put you to the rigth track.

Regards,

MFelix