CODEWITHSUNDEEP
powerbidata-visualizationdax
belle
belle

Reputation: 47

How do I make my Power BI table dynamic to change with the filters the user selects?

I have a table of calls with a column for phone number and call id. I want to create a visual that shows how many callers called how many times. (e.g. 5 callers called once, 4 called twice etc.) The challenge I am facing is that once I calculate the count of the number of callers that called count times, the table is static and is not affected by the filters on the report (e.g. the date filter).

This is what I have done so far. Is there a better way?

CallerNumber CallID DateTime
1 a 2022-01-01
1 b 2022-01-01
2 c 2022-01-02
3 d 2022-01-03
4 e 2022-01-01
4 f 2022-01-05
4 g 2022-01-06

From the above original data, I created a table...

Table1 = 
    SUMMARIZE(
        Query,
        Query[CallerNumber],
        "Call Count", COUNT(Query[CallId])
        )
CallerNumber Call Count
1 2
2 1
3 1
4 3

and then another table from that table which gave me...

Table2 = 
    SUMMARIZE (
        'Table1',
        'Table1'[Call Count],
        "Number of Callers", COUNTROWS('Table1')
    )
Call Count Number of Callers
1 2
2 1
3 1

How would I instead show the below if someone were interested in calls on Jan1?

Call Count Number of Callers
1 1
2 1

Thanks!

Upvotes: 0

Views: 3528

Answers (1)

msta42a
msta42a

Reputation: 3741

CalculatedTable is populated once at powerbi Model refresh, this is why it don't reflect any change in your filters.

A better option is to use a measure:

CountOF = CALCULATE( countrows(VALUES('Table'[CallID])))

Add additional "counter" table with number from 1 to 10 .

enter image description here

how manyCaller = var _virtual = ADDCOLUMNS(VALUES(detail[ids]), "CountOfCalls", [CountOF])
return
CALCULATE( countrows(FILTER(_virtual, [CountOfCalls] = SELECTEDVALUE(counter[CallCount]))))

enter image description here

Upvotes: 1

Related Questions