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 there,
I am looking create a measure "Quote Waiting Customer Review" with multiple rules.
The rules are below.
CANCELLED is a date field.
INVOICED is a date field.
LGI_MAINTENANCE is a date field.
SENT_TO_MIN is a date field.
COMPLETED is a date field.
QUOTED is a date field.
STATUS_DATE is a date linked to calender. I need to call this Var STATUS_DATE = SELECTEDVALUE(Calendar[Date]) in the DAX
COUNT where (CANCELLED is null or CANCELLED >= :STATUS_DATE)
and (INVOICED is null or INVOICED >= :STATUS_DATE)
and (LGI_MAINTENANCE is null or LGI_MAINTENANCE >= :STATUS_DATE)
and (SENT_TO_MIN is null or SENT_TO_MIN >= :STATUS_DATE)
and (COMPLETED is null or COMPLETED >= :STATUS_DATE)
and (ACCEPTED is null or ACCEPTED >= :STATUS_DATE)
and (QUOTED < :STATUS_DATE)
Any help would be appreciated.
Solved! Go to Solution.
@jz5147 did you copy and paste my code into your PBIX and just change the table name from 'Table' to whatever your table is called?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
It has not been solved. Sorry i was meant to click reply and instead clicked accept as solution. Is there any way to reopen?
Hi @jz5147
You can use the SWITCH function to achieve this.
New Measure =
VAR _StatusDate = SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
SWITCH (
TRUE () ,
'Table'[CANCELLED] = "" || 'Table'[CANCELLED] >= _StatusDate , 1 ,
'Table'[INVOICED] = "" || 'Table'[INVOICED] >= _StatusDate , 1 ,
'Table'[LGI_MAINTENANCE] = "" || 'Table'[LGI_MAINTENANCE] >= _StatusDate , 1 ,
'Table'[SENT_TO_MIN] = "" || 'Table'[SENT_TO_MIN] >= _StatusDate , 1 ,
'Table'[COMPLETED] = "" || 'Table'[COMPLETED] >= _StatusDate , 1 ,
'Table'[ACCEPTED] = "" || 'Table'[ACCEPTED] >= _StatusDate , 1 ,
'Table'[QUOTED] < _StatusDate , 1 ,
0 )
Using the above, you can then COUNTROWS / FILTER = 1 on the above.
Hope this helps 🙂
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi Theo,
Thanks for your reply.
'Table'[Cancelled] 'Table'[Invoiced] etc, are all dates in the same table.
How when i write the DAX i cant seem to select or find the these fields?
Thanks
@jz5147 did you copy and paste my code into your PBIX and just change the table name from 'Table' to whatever your table is called?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Yeah I did
@jz5147 can you screen shot what the measure you used?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@jz5147 I am not sure how to reopen a post, but I promise I will work with you until it's resolved 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Thanks Theo!
Please see below
@jz5147 you need to change the all of the 'Table' to whatever the table name is called. For example, if the 'Table' is "AR_MANAGER BI_DASHBOARD_SRC_VW" then you need to replace the 'table' with "AR_MANAGER BI_DASHBOARD_SRC_VW". Try the below:
New Measure =
VAR _StatusDate = SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
SWITCH (
TRUE () ,
'AR_MANAGER BI_DASHBOARD_SRC_VW'[CANCELLED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[CANCELLED] >= _StatusDate , 1 ,
'AR_MANAGER BI_DASHBOARD_SRC_VW'[INVOICED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[INVOICED] >= _StatusDate , 1 ,
'AR_MANAGER BI_DASHBOARD_SRC_VW'[LGI_MAINTENANCE] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[LGI_MAINTENANCE] >= _StatusDate , 1 ,
'AR_MANAGER BI_DASHBOARD_SRC_VW'[SENT_TO_MIN] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[SENT_TO_MIN] >= _StatusDate , 1 ,
'AR_MANAGER BI_DASHBOARD_SRC_VW'[COMPLETED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[COMPLETED] >= _StatusDate , 1 ,
'AR_MANAGER BI_DASHBOARD_SRC_VW'[ACCEPTED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[ACCEPTED] >= _StatusDate , 1 ,
'AR_MANAGER BI_DASHBOARD_SRC_VW'[QUOTED] < _StatusDate , 1 ,
0 )
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Thanks Theo
I understand that but even when i do this, the field [CANCELLED] ETC is greyed out and cant be select. I have checked that those fields are in the same table as where i am creating the meausre. Its actually not picking up the Field when typing the DAX
@jz5147 my apologies, the screenshot has helped my understanding of the problem.
If you were to delete the measure, then re-write it from scratch (instead of copying and pasting), are you able to see columns / fields after the Table name? For example, if you type 'AR_MANAGER BI_DASHBOARD_SRC_VW' does it give you any columns/fields after it as options to select?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi Theo
I have also tried this, not for the same metric above and not as complex.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |