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.
Hi,
I'm have 2 situation here.
1st i'm trying to make some default filtering to PO Amount by Month Chart by Year_Number on slicer. The Year_Number is base on Dim_Date that was generate for 5 years from this year. The idea is the PO Amount by Month Chart must default showing the current year amount like this:
base on this:
this is my DAX this far:
IF(SELECTEDVALUE(Dim_Date[Year_Number]) = YEAR(TODAY());[PO_Amount_Daily];[PO_Amount_Daily])
my concern is when we get on 2019 or 2020, this dax should be SUM the all [PO_Amount_Daily] on the same Month_Abbrev, how to do this right?
the 2nd, i'm trying to make some default filtering to PO Amount by Date Chart by Year_Number on slicer and by PO Amount by Month Chart. Chart must default showing the current year and current month like this:
base on year slicer and month abbrev
this is the DAX:
DefaultMonthSelection2 = IF( COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number]))) && COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev]))) ;[PO_Amount_Daily]; IF( SELECTEDVALUE(Dim_Date[Month_Abbrev]) = FORMAT(TODAY()-1;"MMM") && SELECTEDVALUE(Dim_Date[Year_Number]) = YEAR(TODAY()-1); [PO_Amount_Daily];BLANK() ))
How to do this all right?
Solved! Go to Solution.
Thank's for reply to my question, but i figure it out by my self to solved this problem, FINALY after 5 Days!
here what i do to my case:
1st
I create calculated measure to display the default Amount by Month of this year if the slicer not being selected.
The slicer is filled by column named Dim_Date[Year_Number]
here's the formula:
DefaultSelectionByMonth = IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number]))); SUM([PO_Amount]); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY()))))
this exacly what i neet to showing my data to PO Amount by Month Chart
2nd
Just like the PO Amount by Month Chart, the PO Amount by Date Chart is used calculated measure too, but the condition is by the columns Dim_Date[Year_Number] and Dim_Date[Month_Abbrev]
here's the formula:
DefaultSelectionByDate = IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number]))); IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev]))); SUM([PO_Amount]); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM")))); IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev]))); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY()))); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM"));FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY())))))
and this works!
All the calculated measure is used as value on stack chart. It's Also can be used in any visual that you want to show the default value before some visual or slicer clicked.
Maybe this can help others with the same issued.
Thank You All...
Best regards,
Adrin Pratama
Thank's for reply to my question, but i figure it out by my self to solved this problem, FINALY after 5 Days!
here what i do to my case:
1st
I create calculated measure to display the default Amount by Month of this year if the slicer not being selected.
The slicer is filled by column named Dim_Date[Year_Number]
here's the formula:
DefaultSelectionByMonth = IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number]))); SUM([PO_Amount]); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY()))))
this exacly what i neet to showing my data to PO Amount by Month Chart
2nd
Just like the PO Amount by Month Chart, the PO Amount by Date Chart is used calculated measure too, but the condition is by the columns Dim_Date[Year_Number] and Dim_Date[Month_Abbrev]
here's the formula:
DefaultSelectionByDate = IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Year_Number]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Year_Number]))); IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev]))); SUM([PO_Amount]); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM")))); IF(COUNTROWS(DISTINCT(ALLSELECTED(Dim_Date[Month_Abbrev]))) < COUNTROWS(DISTINCT(ALL(Dim_Date[Month_Abbrev]))); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY()))); CALCULATE(SUM([PO_Amount]);FILTER(ALL(Dim_Date[Month_Abbrev]);Dim_Date[Month_Abbrev]=FORMAT(TODAY();"MMM"));FILTER(ALL(Dim_Date[Year_Number]);Dim_Date[Year_Number]=YEAR(TODAY())))))
and this works!
All the calculated measure is used as value on stack chart. It's Also can be used in any visual that you want to show the default value before some visual or slicer clicked.
Maybe this can help others with the same issued.
Thank You All...
Best regards,
Adrin Pratama
Congratulations.
Hi @cst_dev32
Can you please share the pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards,
Affan
Hi cst_dev32,
Could you share some sample data and explain more details about your requirement?
Regards,
Jimmy Tao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |