How to summarize column with condition on power bi?

Question

I'm trying to create table summary table with following conditions

From the Original table to summary table we have to create using the following conditions

1) select distinct ids
2) select screen name base on highest count group by id and today date
3) If two screens are same value on today date with the same id then pick the first screen

score 1 · Answer 1 · edited Jan 12 '20 at 15:47

1

You could create a Rank calculation using the following formula:

Rank = IF(Original[Start Date]=TODAY(),RANKX(CALCULATETABLE(Original,ALLEXCEPT(Original,Original[ID])),Original[Count]),0)

Output:

You should replace "Original" with your table name in the calculation. Once the Rank is created, you can just filter for Rank=1 and you should have the desired result. Hope this helps.

edited Jan 12 '20 at 15:47

KARTHIKEYAN.A

18,210
6
124
133

answered Jan 09 '20 at 16:49

CR7SMS

2,520
1
5
13

Rank also not working, I'm receiving 0 for the entire column. – KARTHIKEYAN.A Jan 10 '20 at 11:26

score 1 · Accepted Answer · edited Jan 12 '20 at 15:50

1

This yields the desired result as a calculated table.

SummaryTable = 
ADDCOLUMNS(
    ADDCOLUMNS(
        FILTER(
            SUMMARIZE(
                OriginalTable,
                OriginalTable[ID],
                OriginalTable[StartDate]
            ),
            OriginalTable[StartDate] = TODAY()
        ),
        "Count", CALCULATE( MAX( OriginalTable[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [Count]
    RETURN CALCULATE( MIN(OriginalTable[Screen]), OriginalTable[Count] = CurrentCount )
)

Output:

edited Jan 12 '20 at 15:50

KARTHIKEYAN.A

18,210
6
124
133

answered Jan 09 '20 at 19:06

Kosuke Sakai

2,336
2
5
12

Not received count :( – KARTHIKEYAN.A Jan 10 '20 at 05:24
What do you mean? "Count" column in 3rd place above? – Kosuke Sakai Jan 10 '20 at 05:31
I cannot find that requirement in your description above. It is only about extracting rows from Original Table. Can you update the question with more details? – Kosuke Sakai Jan 10 '20 at 06:18
Have you tried this in your local, in my local column header created but not inserted any rows. – KARTHIKEYAN.A Jan 10 '20 at 10:21
```MIN(OriginalTable[Screen]``` what it means? please tell me. – KARTHIKEYAN.A Jan 10 '20 at 11:27
Sorry, I tried this query with today's date but my last updated data is yesterday that is the issue, sorry for the trouble. Thank you so much :) :) <3 <3 <3 – KARTHIKEYAN.A Jan 10 '20 at 12:15

How to summarize column with condition on power bi?

2 Answers2