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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
MCacc
Helper IV
Helper IV

Help with a measure/column to compare values from filters

Hello, 

 

I'm trying to create a mesure/column (whatever works), to retrieve a comparison value between columns in a matrix visual. 

 

I have 4 slicers in total and in each slicer has the same date field.

- This Date field comes from a dummy,

- Meanwhile the values I want to compare come from my fact table. These values are strings, not numbers.

 

The two tables (this dummy and the fact table) are disconnected and can't be in a relationship.

 

Because my user can select a maximum of 4 values, this is what I did:

 

I created this dummy table where I have two columns: Index and Date

 

- Column Date is nothing more than the distinct value of my Date in the fact table

- Column Index is basically generated for each distinct value of of my Date 

 

This is the result:

 

IndexREFERENCEDATE_DT

131/03/2022
130/06/2022
131/12/2021
131/12/2022
231/03/2022
230/06/2022
231/12/2021
231/12/2022
331/03/2022
330/06/2022
331/12/2021
331/12/2022
431/03/2022
430/06/2022
431/12/2021
431/12/2022

 

Then I created 4 slicer objects in my page, and each slicer is filtered by its corresponding Index number.

 

Example for one of the slicers: 

MCacc_0-1699375124687.png

I achieved this in order to work with my Index field, since the user can select whatever they want in any order they want.

 

What I'm really struggling with is to create a measure/column than can compare the values after the selection. 

 

So, for example: 

Date_1    Date_3 

Value RED    Value GREEN

 

..where, Date 1 is my first selection, Date 3 is my second selection. And this can happen in any order, not chronological. 

 

So, Value RED is worse than Value GREEN so my flag is negative . 

 

Something like that...

 

I tried EARLIER function, but the two tables must be in a relationship, as far as I understand. 

I also tried the function OFFSET, but it goes back of a specific number and this can't work because the order of selection isn't always Index - 1. 

 

Please, if you any idea, any suggestion/opinion.. anything is very well accepted. 

 

Thank you so much

 

1 ACCEPTED SOLUTION

Hi, thank you for your suggestion but unfortunately I can't use more than one measure since my visual is matrix type. 

 

Is there any way to use one measure/column instead of four?

 

Thanks anyway.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@MCacc , Seem like you want compare two dates and two index , 1 set of each. You will need 4 disconnected tables 

 

//Date1 and Index 1  is independent tables
new measure =
var _date= maxx(allselected(Date1),Date1[Date])
var _index= minx(allselected(Index1),Index1[Index])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date]= _date  && 'Table'[Index] = _index ))

 

 

//Date2 and Index 2  is independent tables
new measure =
var _date= maxx(allselected(Date2),Date2[Date])
var _index= minx(allselected(Index2),Index2[Index])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date]= _date  && 'Table'[Index] = _index ))

 

 

Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

 

Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi, thank you for your suggestion but unfortunately I can't use more than one measure since my visual is matrix type. 

 

Is there any way to use one measure/column instead of four?

 

Thanks anyway.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.