Forum Discussion
Help with What if Parameter and DateDiff
Hi, ciken ;
If you want to do dynamic parameter values such as 120, you have to use measure. If you want to continue counting, you have to create measure. Here is one way:
1. Manually fill in a table.2.create a measure to calculate count.
Lost count = IF ( ISINSCOPE ( 'Table'[Column1] ), SWITCH ( MAX ( 'Table'[Column1] ), "Lost", COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Lost" ), [date] ), "Retained", COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Retained" ), [date] )), COUNT ( 'Lead'[date] ))3. create a measure to calculate percent of count.
Lost count% = var _count= COUNT ( 'Lead'[date] ) return IF ( ISINSCOPE ( 'Table'[Column1] ), SWITCH ( MAX ( 'Table'[Column1] ), "Lost",DIVIDE( COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Lost" ), [date] ),_count), "Retained", DIVIDE( COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Retained" ), [date] ),_count)), 1)The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, ciken ;
If you want to do dynamic parameter values such as 120, you have to use measure. If you want to continue counting, you have to create measure. Here is one way:
1. Manually fill in a table.
2.create a measure to calculate count.
Lost count =
IF (
ISINSCOPE ( 'Table'[Column1] ),
SWITCH (
MAX ( 'Table'[Column1] ),
"Lost", COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Lost" ), [date] ),
"Retained", COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Retained" ), [date] )),
COUNT ( 'Lead'[date] ))3. create a measure to calculate percent of count.
Lost count% =
var _count= COUNT ( 'Lead'[date] )
return
IF (
ISINSCOPE ( 'Table'[Column1] ),
SWITCH (
MAX ( 'Table'[Column1] ),
"Lost",DIVIDE( COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Lost" ), [date] ),_count),
"Retained", DIVIDE( COUNTX ( FILTER ( ALL ( 'Lead' ), [Retained vs Lost] = "Retained" ), [date] ),_count)),
1)The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for these measures but they did not work. When I took your pbix and included a second Index letter it doesn't change the lost vs retained except for the total count (see picture). Something is still broken that it's not breaking them out uniquely but rather still counting the Lost vs Retained field no matter the index. Since I'm trying to get to a distinct count by account name I need each index (account name) to count distinct. As you can see the percentages are going from total against retained / lost but the numbers themselves did not change.
