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

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.

Reply
ksp1992
Frequent Visitor

Identifiyng which projects have moved to a higher rank (two date snapshots compared)

I have the following data model. 

ksp1992_0-1708693152894.png

And my report have a slicer for both Calendar[Date] and Calendar2[Date]. 

My goal is to create a measure that can be used in a Card-visual and display the number of projects whose rank has decreased (similar to moving forward), when comparing a snapshot from the two chosen date for Calendar2[Date] slicer and Calendar[Date] slicer respectively. 


I have the following measure. It works in a table, but it keeps displaying blank, when I put it in a Card-visual. 

 

Number of Projects Moved Forward = 

VAR _RankNew =
    SUM ( Pipeline[Rank] )
VAR _RankOld =
    CALCULATE (
        SUM ( Pipeline[Rank] ),
        ALL ( 'Calendar' ),
        USERELATIONSHIP ( 'Calendar'[date], Calendar2[date] )
    )
VAR _Difference = _RankOld - _RankNew
VAR _Movement =
    IF (
        ISBLANK ( _RankNew ),
        "Deleted",
        IF (
            ISBLANK ( _RankOld ),
            "New",
            IF (
                _Difference = 0,
                "Not Changed",
                IF ( _Difference > 0, "Forward", "Backward" )
            )
        )
    )
VAR _Tbl =
    FILTER(
    ADDCOLUMNS (
        SUMMARIZE ( Pipeline, Pipeline[project_id] ),
        "Movement", _Movement
    ), _Movement = "Forward")

RETURN
COUNTX(_Tbl, Pipeline[project_id])

 


I guess it is my last return statement that creates the problem. Hope you have an idea how to fix it. 

Below you can see that it works ok in the table (apart from the fact, that it does not sum in the total). 

ksp1992_2-1708694134513.png

 

 





1 ACCEPTED SOLUTION

Hi, 

For trouble-shooting I recommend using DAX query view. Enable it from preview:

ValtteriN_0-1708776592722.png

 

Then place this part of the dax in EVALUATE:
 FILTER(
ADDCOLUMNS (
SUMMARIZE ( Pipeline, Pipeline[project_id] ),
"Movement",IF (
ISBLANK ( _RankNew ),
0,
IF (
ISBLANK ( _RankOld ),
0,
IF (
_Difference = 0,
0,
IF ( _Difference > 0, 1, 0 )
)
)
)),[Movement]=1)

Now if the rows where movement is Forward have value =1 then SUMX should work. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ValtteriN
Super User
Super User

Hi,

Since the total row works in a non-intuitive way this is expected. Basically the filter context in the card visual and total row are the same. I also don't recommend using string in calculations.
In this example I am testing for complete different thing (if [value]>=0.85 then 1 else 0), but the logic is the same.

Here are couple of alternatives:

Test for 85 = SUMX('Table (22)',IF('Table (22)'[Value]>=0.85,1,0))
Test for 85 count = COUNTX(FILTER('Table (22)','Table (22)'[Value]>=0.85),'Table (22)'[Machine])
Test for 85 count var =
var _m = IF(MAX('Table (22)'[Value])>=0.85,1,0)
var _t = FILTER(ADDCOLUMNS('Table (22)',"m",_m),_m=1)
RETURN
COUNTX(_t,[Machine])

Out of these the logic in 3rd example is most closely related to your case. It will return incorrect value on total level (similarly to your case).

ValtteriN_0-1708697369394.png

 

Based on these examples try the following:

VAR _RankNew =
SUM ( Pipeline[Rank] )
VAR _RankOld =
CALCULATE (
SUM ( Pipeline[Rank] ),
ALL ( 'Calendar' ),
USERELATIONSHIP ( 'Calendar'[date], Calendar2[date] )
)
VAR _Difference = _RankOld - _RankNew


VAR _Tbl =
FILTER(
ADDCOLUMNS (
SUMMARIZE ( Pipeline, Pipeline[project_id] ),
"Movement",IF (
ISBLANK ( _RankNew ),
0,
IF (
ISBLANK ( _RankOld ),
0,
IF (
_Difference = 0,
0,
IF ( _Difference > 0, 1, 0 )
)
)
)),[Movement]=1)

RETURN

SUMXX(_Tbl, [Movement])



I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I really appreciate your effort. I have however tried the solution, and it yields the same results as my own. So no summing in either the table nor the card-visual 😞

Hi, 

For trouble-shooting I recommend using DAX query view. Enable it from preview:

ValtteriN_0-1708776592722.png

 

Then place this part of the dax in EVALUATE:
 FILTER(
ADDCOLUMNS (
SUMMARIZE ( Pipeline, Pipeline[project_id] ),
"Movement",IF (
ISBLANK ( _RankNew ),
0,
IF (
ISBLANK ( _RankOld ),
0,
IF (
_Difference = 0,
0,
IF ( _Difference > 0, 1, 0 )
)
)
)),[Movement]=1)

Now if the rows where movement is Forward have value =1 then SUMX should work. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors