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
macwhe
Helper I
Helper I

max per order issue

Hi All,

 

I am struglling with MAX function. I have 2 dimension tables and on Facttable. between 2 dimensions there is a inactive relationship . and between the fact table and FreezeDate Snapshot there is one active relationship

 

First Dimension

Freeze date SnapShot 
1-Sep-21 
2-Sep-21 

 

Second

Freeze Comparison Snaphot 
 

 

31-Aug-21
 
1-Sep-21 

 

facttable

FreezeDate0rderQuantity  
1-Sep-21P100110  
2-Sep-21P100120  
1-Sep-21P10021  
2-Sep-21P100210  
     

 

The goals is to comapre the order qty bewteen those two dates. Lets suppose user selects as Freeze Date snapshot 1s SEP 2021 and Comaprison date is 31 AUG 2021. The results that I need to achieve should be like below . I need to display in visual TABLE.

Freeze date Comparison Snapshotorder Comparison SnapshotQuanity Comparison SnapshotFreeze Date SnapshotOrder Snaphot 

QTY Snapshot

 

Qty Difference Between 2 freezes dates
31-Aug-21emptyEmpty1-Sep-21P100110-10
31-Aug-21EmptyEmpty1-Sep-21P100220-20

 

However the results that I have is : As you can see it shows the max order which is P1002 .

Freeze date Comparison Snapshotorder Comparison SnapshotQuanity Comparison SnapshotFreeze Date SnapshotOrder SnapshotQTY SnaphotQty Difference Between 2 freezes dates
31-Aug-21emptyEmpty1-Sep-21P100210-10

But if I drop the "order column "  from the fact table it shows correctly but I dont want to show the order column from Fact Table

Freeze date Comparison Snapshotorder Comparison SnapshotQuanity SnapshotFreeze Date SnapshotOrder QTYQty Difference Between 2 freezes dates"Order"
31-Aug-21emptyEmpty1-Sep-21P100110-10P1001
31-Aug-21EmptyEmpty1-Sep-21P100220-20P1002

 

these are my measures

Order Comaprison Snaphot=Calculate (
Max(Order).Facttable, Remove filters (FreezeDate Snapshot),
UserRelationship(freezedateSnapshot), (freezedateComparison Snapshot)

Qty Comparison =Calculate (
SUM(Qty).Facttable, Remove filters (FreezeDate Snapshot),
UserRelationship(freezedateSnapshot), (freezedateComparison Snapshot)

Order Snaphot=Max(Order).Facttable

QTY Snapshot=sum(Qty).facttable

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @macwhe,

 

Try measure as:

Measure = 
IF(
    MAX(Facttable[FreezeDate])>=ALLSELECTED('Second Dimension'[Freeze Comparison Snaphot]) && MAX(Facttable[FreezeDate])<=ALLSELECTED('First Dimension'[Freeze date SnapShot]),
    1,
    0
)

Here is the output:

vxulinmstf_0-1631606116902.png

The demo is attached, please try it.

 

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

View solution in original post

5 REPLIES 5
v-xulin-mstf
Community Support
Community Support

Hi @macwhe,

 

Try measure as:

Measure = 
IF(
    MAX(Facttable[FreezeDate])>=ALLSELECTED('Second Dimension'[Freeze Comparison Snaphot]) && MAX(Facttable[FreezeDate])<=ALLSELECTED('First Dimension'[Freeze date SnapShot]),
    1,
    0
)

Here is the output:

vxulinmstf_0-1631606116902.png

The demo is attached, please try it.

 

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

lbendlin
Super User
Super User

Are these snapshot dates predefined or would it be easier to let the user select the date range via a slicer?

HI, the dates are selected by user  Below are the dates that I have available in the slicers. Basically  we have a new freeze date everyday depending on the business and at end of the month we also have a freeze. I forgot to mention I am using Direct Query. thanks a lot slicersslicers

My question was if you could combine both slicers into one date range slicer.

 I cannot combine both slicers the date range , I need to have 2 slicers.

Thanks

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.