SOLVED

Creating Dynamic Dax measure for a virtual table to filter table instead of calculated table(static)

Brass Contributor

Hi,

 

I have a below table in power bi:

CreatedOnDataIdDataset IdNameFloor Area
28/10/2023 01:177057561Site 12342
28/10/2023 01:177057532Site 276829
28/10/2022 01:177057522Site 275775
28/10/2021 01:177057512Site 274721
28/10/2023 01:177057503Site 373667
28/10/2022 01:177057493Site 372613
28/10/2021 01:177057483Site 371559

 

My requirement is to create a virtual table using a dax measure(not calculated table) to filter this table by the first records for each dataset id that are less than or equal to filtered date(CreatedOn).

 

For example,if the above table is filtered for below date:

Parameter/Slicer

01/01/2023

 

Then the resulting virtual table from the dax measure is as below:

CreatedOnDataIdDataset IdNameFloor Area
28/10/2022 01:177057522Site 275775
28/10/2022 01:177057493Site 372613

 

The resulting virtual table ignored 705751 &705748 DataId because our dax measure should select only the first records from the multiple Dataset Id that exist

 

PFA screenshot of the above tables and result:

Excellove15_0-1701334704663.png

 

PFA sample file in below link

Virtual_Dax.pbix

 

Thanks in advance! 

@SergeiBaklan 

 

4 Replies
best response confirmed by Excellove15 (Brass Contributor)
Solution

@Excellove15 

In the sample CreatedOn is returned as text, thus I added Createddate column as DATEVALUE() from it

image.png

Bar date is added as measure

image.png

Depends on your actual model, you may take it as SELECTEDVALUE() on Date table, whatever.

Filtered table is calculated as

Filtered Table = 
VAR belowDate =
    FILTER ( Main, Main[CreatedDate] < [pDate] )
VAR setRowId =
    ADDCOLUMNS (
        Main,
        "Id",
            ROWNUMBER (
                belowdate,
                ORDERBY ( Main[DataId], DESC ),
                PARTITIONBY ( Main[Dataset Id] )
            )
    )
VAR firstRows =
    FILTER ( setRowId, [Id] = 1 )
VAR result =
    SELECTCOLUMNS (
        firstRows,
        "CreatedDate", Main[CreatedDate],
        "DataId", Main[DataId],
        "Dataset Id", Main[Dataset Id],
        "Name", Main[Name],
        "Floor Area", Main[Floor Area]
    )
RETURN
    result

Resulting table is

image.png

You may return firstRows variables if don't care about extra Id column.

Hi @SergeiBaklan ,

 

Many thanks for your quick reply sir !:smile:

This works like a gem!

only thing i expect is, can we convert this as a measure that doesn't return a physical table. Because when we use a calculated table in a report, it causes performance issues. So when I copy this calculated table measure and create a new measure and paste this measure to create a new measure, i am getting below error

Excellove15_1-1701429746475.png

 

So i created a dax measure as below to achieve the same in my sample file:

Measure 6 = calculate(max('cumulus_licensee estatemanagement_contacts'[CreatedOn]),FILTER('cumulus_licensee estatemanagement_contacts',
'cumulus_licensee estatemanagement_contacts'[CreatedOn] <= SELECTEDVALUE('Calendar'[Date])))

 

My expected output would be a visual as below, it should filter the entire visual for the max date when we drag that measure either into visual or filter pane. For example, in this case max date is '9/16/2021 11:59:07 PM', but instead it gives all found date for corresponding EMSQL_ARNOLDCLARK-101. we only need max date.

Excellove15_3-1701433742694.png

 

Could you please help us to resolve this either a new measure or modify the above measure?

we only need a dax measure instead of calculated table measure that returns a physical table.

 

PFA sample file in below link

Timetravel.pbix

 

Thanks in advance!

 

 

Hi @SergeiBaklan ,

 

I have somehow found out the solution(Dax measure) as below:

Measure 2 =
CALCULATE (
    MAX ( 'cumulus_licensee estatemanagement_contacts'[CreatedOn] ),
    FILTER (
        ALLSELECTED ( 'cumulus_licensee estatemanagement_contacts' ),
        'cumulus_licensee estatemanagement_contacts'[CreatedOn]
            <= SELECTEDVALUE ( 'Calendar'[Date] )
    )
)
output:

Excellove15_2-1701942242646.png

 

But when I drag in Name column from 'cumulus_licensee estatemanagement_contacts' table into the visual, the records split into two rows because there are multiple(in this case 2) records for Name column for that particular DBName_id.

My expected output would be like the below:

Excellove15_3-1701942324492.png

 

Also, I am trying to find the count of DBName_id for the above conditions(selected columns,filter conditions),

Below is the dax measure i tried for it:
Count =
COUNTROWS (
FILTER (
SUMMARIZE (
'cumulus_licensee estatemanagement_contacts',
'cumulus_licensee estatemanagement_contacts'[DBName_id]
),
[Measure 2] > 0
)

)

For some reason it's not working correctly. 

I am also trying to find count of distinct DBName-Point_id from the cumulus_licensee estatemanagement_points table

 

Please help me to resolve this issue

 

PFA file here Timetravel (1).pbix

 

Thanks in advance!

 

 
 

Hi @SergeiBaklan 

 

Thanks for your prompt response!

I have got the solution for this and thanks for your patience:smile:

Your solution was the backbone of what i have found! many thanks!

I will accept your solution as the best response and close this query

Please let me know if i can do anything for you!

 

Thanks!

1 best response

Accepted Solutions
best response confirmed by Excellove15 (Brass Contributor)
Solution

@Excellove15 

In the sample CreatedOn is returned as text, thus I added Createddate column as DATEVALUE() from it

image.png

Bar date is added as measure

image.png

Depends on your actual model, you may take it as SELECTEDVALUE() on Date table, whatever.

Filtered table is calculated as

Filtered Table = 
VAR belowDate =
    FILTER ( Main, Main[CreatedDate] < [pDate] )
VAR setRowId =
    ADDCOLUMNS (
        Main,
        "Id",
            ROWNUMBER (
                belowdate,
                ORDERBY ( Main[DataId], DESC ),
                PARTITIONBY ( Main[Dataset Id] )
            )
    )
VAR firstRows =
    FILTER ( setRowId, [Id] = 1 )
VAR result =
    SELECTCOLUMNS (
        firstRows,
        "CreatedDate", Main[CreatedDate],
        "DataId", Main[DataId],
        "Dataset Id", Main[Dataset Id],
        "Name", Main[Name],
        "Floor Area", Main[Floor Area]
    )
RETURN
    result

Resulting table is

image.png

You may return firstRows variables if don't care about extra Id column.

View solution in original post