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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ritaf
Responsive Resident
Responsive Resident

Mesure by date ignoring slicer by another date

Capture.JPG

 

The task is as follows:
- there is a filter that filters our data set (the lower table) by "Slice Date".
- there is a visual element that displays 2 columns - "The amount on the document date" (it is calculated on the document date and the slicer should not change - the condition for calculating the " Slice date"="Document date").
The second column is the current amount, which is calculated on the slice date in the slicer selection (that is, this indicator depends on the filter and changes).
How do I make a measure for "Amount on document date", which operators to use?

 

 

8 REPLIES 8
v-shex-msft
Community Support
Community Support

HI @Ritaf,

Are there any suggestions suitable for your scenario? If that is the case, you can consider kudo or accept it to help others who face a similar scenario.
If not, please share some more detailed information to help us clarify your scenario.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
v-shex-msft
Community Support
Community Support

Hi @Ritaf,

If you want to achieve some advanced filer effect, I'd like to suggest you break the relationship that you do not want to directly apply to your table.
Then you can write a measure expression to check and compare the selections and use on the 'visual level filter' to filter records.

Applying a measure filter in Power BI 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ReyCarter
Helper I
Helper I

please tell me how this can be done?

Ritaf
Responsive Resident
Responsive Resident

Hi, thank you for fast response.

There is no dimDate table.

Just fact table please see pbix in the following link

https://dropmefiles.com/Se4Qh

and an attached pict.Capture.JPG

 

Hey @Ritaf and @ReyCarter ,

 

I'm not if I fully understood what you want.

The following measure is giving you back the sum of Pokaz1 where the [Document date] = [Slice date]:

Amunt on Document date =
VAR vSlicerDate =
    SELECTEDVALUE( 'Table'[Slice date] )
VAR vAmount =
    CALCULATE(
        SUM( 'Table'[Pokaz1] ),
        ALL( 'Table'[Slice date] ),
        'Table'[Document date] = vSlicerDate
    )
RETURN
    vAmount

 

Is that what you wanted?

Otherwise, just let me know if the measure needs changes.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

No, it's not.

It is necessary that when filtering "Slicer by date", the rows do not disappear.
Look at the picture below, where the arrows indicate what values should be obtained in the measure, while this measure should not react to the "Slicer by date"


ReyCarter_0-1616669407537.png

 

amitchandak
Super User
Super User

@Ritaf , is the slicer date is coming from an independent table ?

 

If so you can use

measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = minx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(Sum(Table[value] ), filter(Table, Table[document date] >=Min && Table[document date] <=Max ))

 

if something is joined with slicer date you can get

Sum(Table2[value] )

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

also such a task

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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