Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two measures that we are currenlty using for date ranges. We have a scenario that was missed, where an end date is earlier than the start date. I've tried a couple of things already. Just need some suggestions. Thanks!
NameOfMeasure = SWITCH(TRUE(),AND ([NameOfMeasure]>=0,[NameOfMeasure]<=30),"0-30",
AND ([NameOfMeasure]>=31,[NameOfMeasure]<=60),"31-60",
AND ([NameOfMeasure]>=61,[NameOfMeasure]<=90),"61-90",
AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120","121+")
NameOfMeasureRank = SWITCH('Table'[Column],"0-30",1,"31-60",2,"61-90",3,"91-120",4,"121+",5)
Solved! Go to Solution.
this post was helpful, but I didn't use the proposed solution because I ended up altering the existing Switch statement when I realized the last line was just a catch all for all that was left:
From:
AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120","121+")
To:
,AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120"
,AND ([NameOfMeasure]>=121,[NameOfMeasure]<=1000000),"121+")
Sorry those are columns NOT measures!
Hi @Kgathright , what's your expected result with these columns? I cannot understand what you want to get. Can you provide some sample data and your desired result?
In the graph this data is using, we are calculating how long someone was on an assignment in days:
0-30 days
31-60 days
61-90 days
91-120 days
So there is a total count for each cycle for date parameters on the report. What I need to do is prevent the records that have a negative value to be counted in this graph. Users that had an assignment that eneded before it even started.
Example:
Start Date = 1/15/2020
End Date = 1/12/2020
if you want to count days which don’t include negative days value, you can create a measure like below. It has filtered out negative values before calculate the sum total
Days without negative value = CALCULATE(SUM(Table1[Days]),Table1[Days] >= 0)
You will see the difference below.
You can also remove the negative values in a column if you like.
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
this post was helpful, but I didn't use the proposed solution because I ended up altering the existing Switch statement when I realized the last line was just a catch all for all that was left:
From:
AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120","121+")
To:
,AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120"
,AND ([NameOfMeasure]>=121,[NameOfMeasure]<=1000000),"121+")
@Kgathright Glad to see that you have found the solution. You may accept the appropriate post as the solution to close this thread and help others find it easily. Thanks a lot.
Best regards,
Jing
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |