Russell-PBI Tracker

Re: SUMX of dimension, exclude fact rows with no related marketvalue

Russell-PBI — Thu, 24 Feb 2022 14:59:50 GMT

Hi Anonymous, can you show us the DAX for how you're solving it using a crossfilter as it might help us to better understand what you're trying to achieve?

Re: Create pallets with series of vaule not exceed the certain sum per pallet by using fewest pallets

Russell-PBI — Thu, 24 Feb 2022 14:48:54 GMT

Hi Priyange, off the top of my head, I'd expect some sort of looping to be required, e.g., pick the tallest first, then find the next tallest that fits in the remainder and so on until filled, then start a new set.

It may be possible in Power BI and someone much cleverer than me may know how, but, just in case, do you have any other tools available to you to do the logic for this prior to pulling it into Power BI?

Re: Drillup/Drilldown function not working in matrix visual when multiple measures selected from slicer.

Russell-PBI — Thu, 24 Feb 2022 14:26:57 GMT

Hi Anonymous, can you provide some screenshots of what you mean as I'm not sure at the moment?

Re: Line values is not correct with the if dax from slicer value

Russell-PBI — Thu, 24 Feb 2022 14:21:20 GMT

Hi Anonymous, does this work?:

Avg = IF( CALCULATE( DISTINCTCOUNT('Fund Type'[Fund Type]) , ALLSELECTED('Fund Type'[Fund Type]) ) > 1 , 50000 , 200 )

Re: Line values is not correct with the if dax from slicer value

Russell-PBI — Thu, 24 Feb 2022 09:59:39 GMT

Hi Anonymous, you have Fund Type as your legend, which might be stopping your measure from evaluating as TRUE.

Re: My Matrix Row Values are Incorrect

Russell-PBI — Wed, 23 Feb 2022 09:15:23 GMT

Here is the solution I came up with:

Using as a filter value:

Max Obligated Flag = VAR MaxObligatedValue = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN IF(Table1[Obligated] = MaxObligatedValue, TRUE, FALSE)

Or, using as a grouping attribute:

Max Value by Record Number = VAR MaxObligated = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN CALCULATE(MAX(Table1[Value]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod]), Table1[Obligated] = MaxObligated)

Re: Carry over previous year + accumulation current year

Russell-PBI — Mon, 21 Feb 2022 13:59:52 GMT

Well, that's wacky looking. Maybe put the various columns in a table and see what's happening with the individual sums against DateOfApplication. It worked when I tested it with your sample data, so maybe there's something else going on. The sudden ups and downs on the graph suggest it's adding the previous application count for some days but not others.

Re: Distinct Count by filter

Russell-PBI — Mon, 21 Feb 2022 12:33:49 GMT

tamerj1's posted solution works with a calculated column and measure, so no messing about with PowerQuery. I'd say that's a good solution for you.

Re: Retaining certain values so they can be compared against

Russell-PBI — Sun, 20 Feb 2022 19:16:44 GMT

Check out the link in my post 😉

Re: Retaining certain values so they can be compared against

Russell-PBI — Sun, 20 Feb 2022 00:45:11 GMT

Hi Anonymous, try this: https://drive.google.com/file/d/1-2UHhq7J4C5Y0csqQCrr3nJMhX12DJVt/view?usp=sharing

Add a new flag to identify the fixed areas and add a new table for just the optional area values, then use the 'Show Area' measure as a 'greater than 0' filter on your visual:

Show Area = 
CALCULATE(
COUNTROWS('Table')
,'Table'[Fixed Flag] = TRUE() 
|| ISFILTERED('Optional Area'[Optional Area]) = TRUE()
&& 'Table'[Area] IN ALLSELECTED('Optional Area'[Optional Area])
, 'Table'[Area] = SELECTEDVALUE('Table'[Area])
)

Feels fairly wonky, but if it works, it works. Remember to lock and hide the filter, so it can't be removed.

Re: For a Given ID#, 1-N Sequence#, and Status Column, Return the Min of Seq. for Rows Where Status =""

Russell-PBI — Sat, 19 Feb 2022 20:40:48 GMT

Is there anything different about your table compared with the table you originally posted? Perhaps more columns? If so, this slight change might work better:

Column = 
VAR MinSequence =
CALCULATE(
Min('Table'[Sequence])
 , ALLEXCEPT('Table','Table'[ID])
 , 'Table'[Status] = BLANK()
 )
RETURN
SWITCH(
TRUE
, 'Table'[Status] = BLANK()
 && 'Table'[Sequence] = MinSequence
, "In Progress"
, 'Table'[Status] = BLANK()
 && NOT 'Table'[Sequence] = MinSequence
, "To Do"
, 'Table'[Status]
)

For info, the previous formula was only ignoring Sequence when finding the minimum Sequence value across matching IDs. The second formula ignores everything but the ID (this part: ALLEXCEPT('Table','Table'[ID]))

Re: Calculated Column - Show Last Week's Data in Current Week

Russell-PBI — Sat, 19 Feb 2022 01:34:04 GMT

Hi GSPBI, this seems to work:

No. Activities Planned = 
VAR PublicationNumber = 'Table'[Publication Number]
VAR PreviousPublicationNumber = 
CALCULATE(
Max('Table'[Publication Number])
 , ALLEXCEPT('Table','Table'[Id])
 , ('Table'[Publication Number] < PublicationNumber)
 )
RETURN
CALCULATE(
MAX('Table'[Planned])
, ALLEXCEPT('Table', 'Table'[Id])
, 'Table'[Publication Number] = PreviousPublicationNumber
)

Re: Carry over previous year + accumulation current year

Russell-PBI — Sat, 19 Feb 2022 00:53:26 GMT

Hi Anonymous, does this work?:

PreviousYearApplications = 
VAR StudyYear = MAX(Final_data[Year of StudyStart2])
VAR PrevAppYear = MAX(Final_data[Year of Application2]) - 1
RETURN
CALCULATE(
Final_data[NumberOfApplications]
, ALL(Final_data)
, Final_data[Year of StudyStart2] = StudyYear
, Final_data[Year of Application2] = PrevAppYear
)

Accumulated + Previous Year = Final_data[AccumOfAplications2022] + [PreviousYearApplications]

EDIT: This may be better for when you have multiple years:

PreviousYearApplications = 
VAR StudyYear = MAX(Final_data[Year of StudyStart2])
VAR AppYear = MAX(Final_data[Year of Application2])
RETURN
CALCULATE(
Final_data[NumberOfApplications]
, ALL(Final_data)
, Final_data[Year of StudyStart2] = StudyYear
, Final_data[Year of Application2] < AppYear
)

Re: Counting Net New and Net Loss

Russell-PBI — Sat, 19 Feb 2022 00:11:31 GMT

Hi Anonymous, does this work?:

Unprocessed = 
VAR MaxDate = CALCULATE(MAX('Table'[File Date]), ALL('Table'))
VAR Unprocessed = CALCULATE(
DISTINCTCOUNT('Table'[Unique Payment ID])
, 'Table'[File Date] = MaxDate
)
RETURN
IF(ISBLANK(Unprocessed), 0, Unprocessed)

Processed =
DISTINCTCOUNT('Table'[Unique Payment ID]) - [Unprocessed]

Re: Average a measure's results for a group

Russell-PBI — Fri, 18 Feb 2022 23:44:41 GMT

Hi careisin60, something like this?:

Average Customers = 
VAR TeamLeader = MAX('Table'[Team Leader])
VAR TeamLeaderCustomerCount = CALCULATE(
[Count of Cust ID]
, ALL('Table'[Team Member])
, 'Table'[Team Leader] = TeamLeader
)
VAR TeamMemberCount = CALCULATE(
DISTINCTCOUNT('Table'[Team Member])
, ALL('Table'[Team Member])
, 'Table'[Team Leader] = TeamLeader
)
RETURN
DIVIDE(TeamLeaderCustomerCount, TeamMemberCount)

EDIT: I was correct the first time

Re: My Matrix Row Values are Incorrect

Russell-PBI — Fri, 18 Feb 2022 23:16:44 GMT

That's a strange one. From the screenshot alone, I don't see any reason why the rows wouldn't sum up to the total. If it's now dummy data, would you like to share your PBI file and data, and I'll see if I get the same result? You can send me a DM if you'd prefer.

Re: For a Given ID#, 1-N Sequence#, and Status Column, Return the Min of Seq. for Rows Where Status =""

Russell-PBI — Fri, 18 Feb 2022 20:58:02 GMT

Hi maibacherstr, maybe a calculated column would serve?:

Column = 
VAR MinSequence =
 CALCULATE(
 Min('Table'[Sequence])
 , ALL('Table'[Sequence])
 , 'Table'[Status] = BLANK()
 )

RETURN
SWITCH(
TRUE
, 'Table'[Status] = BLANK()
 && 'Table'[Sequence] = MinSequence
, "In Progress"
, 'Table'[Status] = BLANK()
 && NOT 'Table'[Sequence] = MinSequence
, "To Do"
, 'Table'[Status]
)

EDIT: Tidied up with a VAR.

Re: Adding an IF statement to existing Measure to Filter only on 1 Row of results

Russell-PBI — Fri, 18 Feb 2022 20:28:24 GMT

Hi adoster, it depends on what your model looks like as I can see you have at least two tables (All Data and ProcedureList).

It might be possible to solve it with a calculated column. Your IF statement isn't valid, but it looks like you want to exclude all other values except the "Vascular Surgery" service for the "Vascular Surgery" provider group only? If so, you may be able to use a calculated column like this:

Include in Count Flag =
IF(
     'All Data'[Provider Groups] = "Vascular Surgery"
           && RELATED('ProcedureList'[Service]) = "Vascular Surgery"
      || NOT 'All Data'[Provider Groups] = "Vascular Surgery"
,     1
,     0
)

You could then use this as a filter on your visual, page or report; or reference it in your existing measure:

CountByRank =
     VAR res =
          CALCULATE (
               COUNT ( 'All Data'[Procedure_Desc] ),
               FILTER ( 'All Data', 'All Data'[LowestRank] = 1 ),
               ALLEXCEPT ( 'All Data', 'All Data'[Service Date] ),
               'All Data'[Include in Count Flag] = 1
)
RETURN
IF ( ISBLANK ( res ), 0, res )

This may only work if your ProcedureList table is the 'one' side of a one:many relationship.

Re: Sumx? All? Filter?

Russell-PBI — Fri, 18 Feb 2022 18:44:48 GMT

Hi Anonymous, something like this should work:

DIVIDE(
     SUM(SOA[Avg_Rat])
,    CALCULATE(SUM(SOA[Avg_Rat]), ALLSELECTED(SOA[Owner]))
)

It sums Avg_Rat as affected by all attributes (Owner, Yes) and divides that by the sum of Avg_Rat across all Owners but still affected by Yes.

Re: My Matrix Row Values are Incorrect

Russell-PBI — Fri, 18 Feb 2022 17:25:07 GMT

Hi Anonymous, it's a bit difficult to discern with just the table given. Can you provide a model diagram and sample of the data?

In a lot of cases where the total sums up differently to the sum of the individual lines, it is because of the relationship between tables. For example, one record in your measure table may be related to more than one record in your attribute table and those records have different 'Obligated value' values (e.g., $0-$25K and $25K-$250K). The total is unaffected by the attribute, so just counts the number of records in the actual table, whereas the rows are affected by the attribute and will be counted against each attribute value where there is a relationship.