Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sivarajan21
Helper V
Helper V

Create a Dax measure for a visual to filter max date for an Id

Hi,

 

I have created a below calculated table measure and it achieves what I wanted:

Filtered Table_final =
VAR belowDate =
FILTER (
'cumulus_licensee estatemanagement_contacts',
'cumulus_licensee estatemanagement_contacts'[CreatedOn] < [pDate]
)
VAR setRowId =
ADDCOLUMNS (
'cumulus_licensee estatemanagement_contacts',
"key",
ROWNUMBER (
belowdate,
ORDERBY ( 'cumulus_licensee estatemanagement_contacts'[CreatedOn], DESC ) /*PARTITIONBY ( 'cumulus_licensee estatemanagement_contacts'[CreatedOn] )*/
)
)
VAR firstRows =
FILTER ( setRowId, [key] = 1 )
VAR result =
SELECTCOLUMNS (
firstRows,
"CreatedDate", 'cumulus_licensee estatemanagement_contacts'[CreatedOn],
"Id", 'cumulus_licensee estatemanagement_contacts'[Id],
"Dbname_id",
'cumulus_licensee estatemanagement_contacts'[DBName_id] /*"Name", Main[Name],
"Floor Area", Main[Floor Area]*/
)
RETURN
result

 

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 our report that contains millions of rows, it causes performance issues.

So when I copy this calculated table measure and create a new dax measure and paste this measure to create a new measure, i am getting below error

 

sivarajan21_3-1701671694806.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])))

 

Here my single DBName_id will multiple Createdon dates, ex:EMSQL_ARNOLDCLARK-101

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 in visual.

 

The fields used in table visual are:

  • DBName_id from cumulus_licensee estatemanagement_contacts table
  • Createdon from cumulus_licensee estatemanagement_contacts
  • Date slicer comes from Calendar table
 

sivarajan21_4-1701671915337.png

 

 

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

we only need a dax measure that can be used in a  visual instead of calculated table measure that returns a physical table.

 

PFA sample file in below link

Timetravel.pbix

 

Thanks in advance!

@Ahmedx @marcorusso @Greg_Deckler @Ashish_Mathur @amitchandak 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @sivarajan21 ,

 

You can create a measure.

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

vtangjiemsft_0-1701930497317.png

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. 

View solution in original post

5 REPLIES 5
v-tangjie-msft
Community Support
Community Support

Hi @sivarajan21 ,

 

Please use the answer to the current post as a solution, and for other subsequent more questions please start a new thread to be more focused, and for more engineers and users to get involved. Thanks in advance.

 

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. 

Hi @v-tangjie-msft 

 

I agree! 

I will close this query and accept your solution😊

 

Thanks

v-tangjie-msft
Community Support
Community Support

Hi @sivarajan21 ,

 

You can create a measure.

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

vtangjiemsft_0-1701930497317.png

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. 

Hi @v-tangjie-msft ,

 

This is awesome and it works perfectly as shown below!😊

sivarajan21_2-1701941105378.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:

sivarajan21_1-1701941037586.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!

@v-tangjie-msft @Ahmedx 

 

Ahmedx
Super User
Super User

pls try this

Filtered Table_final =
SUMX('cumulus_licensee estatemanagement_contacts',
VAR belowDate =
FILTER (
'cumulus_licensee estatemanagement_contacts',
'cumulus_licensee estatemanagement_contacts'[CreatedOn] < [pDate]
)
VAR setRowId =
ADDCOLUMNS (
'cumulus_licensee estatemanagement_contacts',
"key",
ROWNUMBER (
belowdate,
ORDERBY ( 'cumulus_licensee estatemanagement_contacts'[CreatedOn], DESC ) /*PARTITIONBY ( 'cumulus_licensee estatemanagement_contacts'[CreatedOn] )*/
)
)
VAR firstRows =
FILTER ( setRowId, [key] = 1 )
VAR result =
SELECTCOLUMNS (
firstRows,
"CreatedDate", 'cumulus_licensee estatemanagement_contacts'[CreatedOn],
"Id", 'cumulus_licensee estatemanagement_contacts'[Id],
"Dbname_id",
'cumulus_licensee estatemanagement_contacts'[DBName_id] /*"Name", Main[Name],
"Floor Area", Main[Floor Area]*/
)
RETURN
result)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors