Forum Discussion

Enam's avatar
Enam
Regular Visitor
2 years ago
Solved

DAX help needed

Here is the link to a sample pbix file https://drive.google.com/file/d/1J4C3V4A98IwZpQbubTlF6tPWlwXQG2Ir/view?usp=sharing or to Excel file https://docs.google.com/spreadsheets/d/18SXUttN51bZLLPfKXnV...
Need Help
  • Gabry's avatar
    Gabry
    2 years ago

    Hello Enam 

     

    Do you need a measure or it's ok a calculated column?

     

    This is the formula for calculated column

     

    Count of Activitys =

    var _start = Classeur[Start_Date]
    var _end= Classeur[End_Date]
    var _mon= Classeur[Monday]
    RETURN
    CALCULATE(COUNT(Classeur[Activity]), all(Classeur),Classeur[Start_Date]<=_mon, Classeur[End_Date]>=_mon)
     
    Instead this could be your measure
     
    Count of Activities Measure =
    VAR _mon = SELECTEDVALUE(Classeur[Monday])
    RETURN
    CALCULATE(
        COUNT(Classeur[Activity]),
        ALL(Classeur),
        Classeur[Start_Date] <= _mon,
        Classeur[End_Date] >= _mon
    )

    let me know 😉
  • Gabry's avatar
    Gabry
    2 years ago

    I see. In that case I think you need to create another table, a Mondays table, use it in the column. I attach a sample pbix

     

    I think like this can work but I don't know if it's a feasible solution for you. Let me know, maybe I can think of other solutions

Gabry's avatar
Gabry
Super User
2 years ago

Hello Enam 

 

Do you need a measure or it's ok a calculated column?

 

This is the formula for calculated column

 

Count of Activitys =

var _start = Classeur[Start_Date]
var _end= Classeur[End_Date]
var _mon= Classeur[Monday]
RETURN
CALCULATE(COUNT(Classeur[Activity]), all(Classeur),Classeur[Start_Date]<=_mon, Classeur[End_Date]>=_mon)
 
Instead this could be your measure
 
Count of Activities Measure =
VAR _mon = SELECTEDVALUE(Classeur[Monday])
RETURN
CALCULATE(
    COUNT(Classeur[Activity]),
    ALL(Classeur),
    Classeur[Start_Date] <= _mon,
    Classeur[End_Date] >= _mon
)

let me know 😉
Enam's avatar
Enam
Regular Visitor
2 years ago

Hello @Gabry , the total is ok but not the dispatch by Activity even with a slight change to the measure you kindly provided

Count of Activities Measure =
VAR _mon = SELECTEDVALUE(Classeur[Monday])
RETURN
CALCULATE(
    COUNT(Classeur[Activity]),
    ALLEXCEPT(Classeur, Classeur[Activity]),
    Classeur[Start_Date] <= _mon,
    Classeur[End_Date] >= _mon
)

 

 

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    I see. In that case I think you need to create another table, a Mondays table, use it in the column. I attach a sample pbix

     

    I think like this can work but I don't know if it's a feasible solution for you. Let me know, maybe I can think of other solutions