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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MCacc
Helper III
Helper III

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

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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