Dynamic calculatable table

[BBConsultancy]

Hi,

I am working on a report for a restaurant. I was able to visualize the Frequency per visitor count in a bar chart. 

1. I created a calculated table:

VisitCountSummary =

SUMMARIZE(

    Reserveringen,

    Reserveringen[Email],

    "VisitorCount", [Aantal geaccepteerde reserveringen]

)

2. I created a measure:

VisitCountFrequency =

COUNTROWS(

    FILTER(

        VisitCountSummary,

        VisitCountSummary[VisitorCount] = SELECTEDVALUE(VisitCountSummary[VisitorCount])

    )

)

Is there a way to make the VisitCountSummary calculatable table dynamic? For example, when I filter on year = "2023" I want the calculated table to change and only take reservations from 2023 into account. This way I can see how many times people return to the restaurant in one year.

Kind regards,

Brent

[Anonymous · Accepted solution]

Hi @BBConsultancy ,

You can use the dates of a date table as a slicer, please note that the table cannot have a model relationship with other tables.

(1) This is my test data.  

Reserveringen Table:

Date table:

Datum = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

(2) We can create a calculated table, and you can see that when we use dynamic parameters in the calculated table, the calculated table outputs empty, because the calculated table is static.

For example, if the date we want to select for the slicer is 2024/3/1, we write a fixed parameter to the calculation table to show the table data if the slicer selects 2024/3/1.

Due to known limitations, the calculated table is static, but we can place it as a variable in a measure, and we can create a variable storage dynamic virtual table. For example, let's get the VisitorCount value based on the date selected by the slicer. Then we can write DAX like this

VisitCountFrequency = 
var _VisitCountSummary =
SUMMARIZE(
    Reserveringen,
    Reserveringen[Email],
    Reserveringen[Reservation date],
    "VisitorCount", [Aantal geaccepteerde reserveringen]
)

var _table2 =
FILTER(
    _VisitCountSummary, [Reservation date]=SELECTEDVALUE(Datum[Date])
)
RETURN MAXX(_table2,[VisitorCount])

You can see that the result is 2. At this time, we can look at the table Table2 again, and the result of VisitorCount is also 2.

In the same way, we can create a calculation table with a fixed filter parameter of 2024/3/3. Then select 2024/3/3 in the slicer and see if the results of the measure match the results in the calculation table to confirm that the DAX function is correct.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hi @BBConsultancy ,

Thanks tamerj1  for the quick reply. I have some other ideas to add:

As far as I know, the calculation table is static and doesn't support dynamic display in the data pane for now. But you can use the calculation table as a variable in the metric and he will generate a dynamic virtual table.

The DAX is shown below for your reference:

1.First we need to create a table of years as a slicer.

YearTable = GENERATESERIES(1880,2026,1)

2. Create a measure. _table2 is a dynamic virtual table where you can create measures on demand.

VisitCountFrequency =
var _VisitCountSummary =
SUMMARIZE(
    Reserveringen,
    Reserveringen[Email],
    Reserveringen[Year],
    "VisitorCount", [Aantal geaccepteerde reserveringen]
)
var _table2= FILTER(_VisitCountSummary,[Year]=SELECTEDVALUE('YearTable'[Value])

RETURN .....

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[BBConsultancy]

Hi Neeko,

thank you for your reply. I already have a date table in my data set, so I guess it is not necessary to create the year table as you describe in step 1?

based on your answer I made the DAX formula below:

VisitCountFrequency =

var VisitCountSummary =

SUMMARIZE(

    Reserveringen,

    Reserveringen[Email],

    Reserveringen[Reservation date],

    "VisitorCount", [Aantal geaccepteerde reserveringen]

)

var table2 =

FILTER(

    VisitCountSummary, [Reservation date]=SELECTEDVALUE(Datum[Date])

)

RETURN

CALCULATE(

    [Aantal geaccepteerde reserveringen], table2

)

It's not working, which does not surprise me because I am pretty sure I am interpeting your solution wrong.

[tamerj1]

Hi @BBConsultancy 
Please try 

VisitCount =
SELECTCOLUMNS ( GENERATESERIES ( 1, 1000, 1 ), "VisitorCount", [Value] )

VisitCountFrequency =
COUNTROWS (
    FILTER (
        VALUES ( Reserveringen[Email] ),
        [Aantal geaccepteerde reserveringen]
            = SELECTEDVALUE ( VisitCount[VisitorCount] )
    )
)