Forum Discussion

dofrancis3's avatar
dofrancis3
Icon for Resolver I rankResolver I
2 years ago

Mesure and Calendar table

Hi Colleagues! I've created a "Calendar" table, but when I try to create a measure, "Calenda table" doesn't appear. Could you help me to fixe this issue please. In other words, when I call up "Cal...
wynhopkins's avatar
wynhopkins
Icon for Most Valuable Professional rankMost Valuable Professional
2 years ago

You can't directly reference a table or column in a measure.  It's got to be wrapped in a function like a SUM, COUNTROWS, CALCULATE.    

what are you trying to do with the measure

  • dofrancis3's avatar
    dofrancis3
    Icon for Resolver I rankResolver I
    2 years ago
    I would like to calculate the pourcentage of status without "blank dates" and pourcentage "include blank dates"
    below is my Mesure: 
    % Status = DIVIDE([NbrPass], [NbrStatus], 'Calendar'[Date] = BLANK())
     
    Knowing that:
    NbrPass = CALCULATE(COUNTROWS(repository),repository[STATUS]= "Pass")
    NbrStatus = CALCULATE(COUNTROWS(repository),repository[STATUS]= "Pass"||repository[STATUS]= "Fail")

     


     

    • wynhopkins's avatar
      wynhopkins
      Icon for Most Valuable Professional rankMost Valuable Professional
      2 years ago

      Your Calendar table shouldn't have blank dates so don't reference that.


      Number of records  =  COUNTROWS(repository)     
      Q: is there  status other than pass or fail?

      Blank Dates = CALCULATE( COUNTROWS(repository), ISBLANK(repository[Date Column]) )

      Replace Date Column with whichever column you're checkin in the repository status

      % = DIVIDE( [Blank Dates,  [Number of records])




      • dofrancis3's avatar
        dofrancis3
        Icon for Resolver I rankResolver I
        2 years ago

        Dear Coleagues;

        I would like to Summarize "sampled" colomn only if the "date" is not blank 

        Be low my mesure

        Tot_sampled =
        VAR __numberOfInterwd = SUM(LQASrepository[Total sampled),
                                                    && LQASrepository[Start date] <> BLANK ())
        RETURN
        IF(ISBLANK(__numberOfInterwd), "No Data",__numberOfInterwd)
         
        Please support
    • talespin's avatar
      talespin
      Icon for Solution Sage rankSolution Sage
      2 years ago

      hi dofrancis3 ,

       

      This is what I used

       

       ----------------------------------------------------------------------------------------

      Calendar table :

      CALENDAR = CALENDAR("2023-01-01", "2024-12-31")

       ----------------------------------------------------------------------------------------

      % Status without Blank Date =
      VAR _TotalCount = CALCULATE( COUNT(FactTable[ID]), KEEPFILTERS(NOT(ISBLANK('CALENDAR'[Date]))) )
      VAR _PassCount = CALCULATE( COUNT(FactTable[ID]), FactTable[Status] = "Pass" , KEEPFILTERS(NOT(ISBLANK('CALENDAR'[Date]))) )

      RETURN DIVIDE( _PassCount, _TotalCount)

       ----------------------------------------------------------------------------------------

      % Status with Blank Date =
      VAR _SelYear = SELECTEDVALUE('CALENDAR'[YEAR])
      VAR _TotalCount = CALCULATE( COUNT(FactTable[ID]) , REMOVEFILTERS('CALENDAR'[Date]), 'CALENDAR'[YEAR] = _SelYear || ISBLANK('CALENDAR'[YEAR]) )
      VAR _PassCount = CALCULATE( COUNT(FactTable[ID]), FactTable[Status] = "Pass" , REMOVEFILTERS('CALENDAR'[Date]), 'CALENDAR'[YEAR] = _SelYear || ISBLANK('CALENDAR'[YEAR]))

      RETURN DIVIDE( _PassCount, _TotalCount)

       ----------------------------------------------------------------------------------------